How can I count the number of cells with a particular color/shade

[Lynn]

I have a spreadsheet where I need to count the number cells with each of
these colors/shades: Yellow, Orange, and Red

Thanks, Lynn

 

[Joel]

You need a UDF function like the one below

Function CountColors(Target As Range, CellColor As String)
Select Case CellColor
Case "Yellow"
SearchColor = 6
Case "Red"
SearchColor = 3
Case "Orange"
SearchColor = 9
End Select

CountColors = 0
For Each cell In Target
If Range("A1").Interior.ColorIndex = SearchColor Then
CountColors = CountColors + 1
End If
End If
End Function

 

[Joel]

I made a small typo

Function CountColors(Target As Range, CellColor As String)
Select Case CellColor
Case "Yellow"
SearchColor = 6
Case "Red"
SearchColor = 3
Case "Orange"
SearchColor = 9
End Select

CountColors = 0
For Each cell In Target
If Range("A1").Interior.ColorIndex = SearchColor Then
CountColors = CountColors + 1
End If
Next cell
End Function

 

[Dave Curtis]

Hi,

You'll need a macro/UDF for this. have a look at Chip's site.

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

Dave

url:http://www.ureader.com/msg/104234665.aspx