Count coloured cells?

[VAILJ4]

Can anyone tell me, in very simple terms, how to count cells which are
formatted the same fill colour in a worksheet? Like, count the red ones, count
the blue ones etc.
Thanks in advance.

 

[Max]

Try MVP Chip Pearson's page at:

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

Look for "Counting Cells With A Specific Color"

 

[VAILJ4]

Thanks, I'll see if I can decipher it.

 

[Bob Phillips]

Check this previous post for a complete solution


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

To complete that ...

Check this previous post for a complete solution

http://tinyurl.com/2u22g


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

You're welcome, Vailj4 !

 

[VAILJ4]

Thanks Bob and everybody else

 

[L. Howard Kittle]

Here is one way, though Chip's are PRO examples.

Does not work with Conditional Formatting.

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