How do I sort or count cells by fill color?

[Guest]

I have a spreadsheet that contains pasted data from an online database. The
imported cells are color coded with RED, YELLOW, and GREEN fill. I would
like an automated way to calculate the number of RED, YELLOW, and GREEN cells
in the data. Any thoughts?

 

[Guest]

Hi

Have a look here:
http://www.cpearson.com/excel/colors.htm

Andy.

 

[L. Howard Kittle]

Chip's site will give you everything to do that. For grins, here's my shot
at it. Adjust to suit your range.
Will not work if color is from 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