need help in cells count According to color

[talhv4]

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]

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

[SamT]

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]

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]

Thanks for all your help the code works really good thnx.