PDA

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

SamT
08-16-2015, 06:32 AM
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.