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

Asked By Lyn on 04-Jun-08 10:17 PM
I have a spreadsheet where I need to count the number cells with each of
these colors/shades: Yellow, Orange, and Red

Thanks, Lynn




Joe replied on 05-Jun-08 12:46 AM
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
Joe replied on 05-Jun-08 12:46 AM
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
Dave Curtis replied on 05-Jun-08 03:51 AM
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