Calculate number of cells colored red

Closed
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
Can someone help me calculate a formula to calculate number of cells that are a certain color?

Thanks,

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
try this macro

Sub test()
Dim r As Range, c As Range, j As Integer, m As Integer
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In r
If c.Interior.ColorIndex <> -4142 Then
j = c.Interior.ColorIndex
MsgBox j
GoTo exitloop
End If
Next c
exitloop:
m = 0
For Each c In r
If c.Interior.ColorIndex = j Then m = m + 1
Next c
MsgBox "count of cell in color " & j & "  is   " & m
End Sub