Counting Cells by Format

[jansroots]

I have some large spreadsheets with rows of expected results for a specific
data element under various conditions. People entered whether or not the
expected results were received by changing the fill on the cell to green,
yellow or red.

I need to count the different results into Pass, Doc, and Fail. Help!!

Here is the situation:

X-Axis = Element Name
Y-Axis = Condition.
Cells contain either Y; N; or n/a

Row 1 has 5 cells = Red; 150 cells = Green; 30 cells = Yellow.
Row 2 has 15 cells = Red; 120 cells = Green; 50 cells = Yellow.
How don't care about the Cell values, but do care about the results (color).

 

[FSt1]

hi
you need a udf for that. see this site. look for countbycolor
http://cpearson.com/excel/colors.aspx

there is a sum by color too.

Regards
FSt1

 

[L. Howard Kittle]

Here is a version to that, will need some tweaking adapt to the range of
cells your are coloring. Looks like you can eliminate Blue5 also. Does not
work with conditional formatting colors.

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