View Full Version : [SOLVED:] need help in cells count According to color
talhv4
08-15-2015, 05:16 AM
hey,
i need help,
how do i count cells by color in vba ?
i only need to cont to vbblue colored cells
plz.help
Trebor76
08-15-2015, 06:00 PM
Hi talhv4,
Welcome to the forum!!
Try this:
Option ExplicitSub Macro1()
Dim dblMyCount As Double
Dim rngMyRange As Range
Dim rngMyCell As Range
Set rngMyRange = Range("A2:D10") 'Range to count cells coloured with vbBlue. Change to suit.
Application.ScreenUpdating = False
For Each rngMyCell In rngMyRange
If rngMyCell.Interior.Color = vbBlue Then
dblMyCount = dblMyCount + 1
End If
Next rngMyCell
If dblMyCount = 0 Then
MsgBox "There are no cells coloured vbBlue in the range " & rngMyRange.Address & ".", vbExclamation
Else
MsgBox "There are " & dblMyCount & " cells coloured vbBlue in the range " & rngMyRange.Address & ".", vbInformation
End If
Set rngMyRange = Nothing
Application.ScreenUpdating = True
End Sub
Regards,
Robert
Hey Trebor,
Nice code, well formed, good use of RPN.
Only thing is, Double is a Decimal number but the count of cells will be a whole number.
Either
Dim dblMyCount As Integer (<=~32K) Uses less memory, important for Windows 98. Faster for some Object Properties.
OR
Dim lngMyCount As Long( > ~32K)
Trebor76
08-16-2015, 04:29 PM
Hi Sam,
Thanks for that. Yes, considering the number of rows now available from Excel 2007, long would probably be the best variable, though double did work fine.
Appreciate the feedback :)
Robert
talhv4
08-17-2015, 02:04 AM
Thanks for all your help the code works really good thnx.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.