Counting Cell Color?

[dwake]

Is it possible to count the number of cells in a column by color? For
example if there are ten cells in a specific column that are colored red, can
I insert a formula to get that sum?

 

[L. Howard Kittle]

Chip can tell you about everything you will need to know on this subject.

http://www.cpearson.com/excel/colors.aspx


Or you can probably edit this down to only count the red cells. You will
need to name ther range to be counted Data.

Sub ColorCount()
'Counts the number of colored
'cells in a range named Data.
Dim Blue5 As Integer
Dim Red3 As Integer
Dim Green4 As Integer
Dim Yellow6 As Integer
Dim Cell As Range

For Each Cell In Range("Data") '("B1:F11")
If Cell.Interior.ColorIndex = 5 Then
Blue5 = Blue5 + 1
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + 1
ElseIf Cell.Interior.ColorIndex = 4 Then
Green4 = Green4 + 1
ElseIf Cell.Interior.ColorIndex = 6 Then
Yellow6 = Yellow6 + 1
End If
Next

Range("A1").Value = Blue5 & " Blue"
Range("A2").Value = Red3 & " Red"
Range("A3").Value = Green4 & " Green"
Range("A4").Value = Yellow6 & " Yellow"

MsgBox " You have: " & vbCr _
& vbCr & " Blue " & Blue5 _
& vbCr & " Red " & Red3 _
& vbCr & " Green " & Green4 _
& vbCr & " Yellow " & Yellow6, _
vbOKOnly, "CountColor"
End Sub

HTH
Regards,
Howard

 

[L. Howard Kittle]

Forgot to say, my code does not work if the cells are colored by conditional
formatting.

Regards,
Howard