Counting cell colours

[jc132568]

Dear experts,
In my worksheets I have a "traffic light" system where cells with particular
values turn assigned cells in another location red, green or yellow using
conditional formatting. This provides me with a quick reference. My
question is can I count cells according to their colouring. Then I could say
I've got 20 cases complete, 10 cases in process, and 2 cases pending for
example. I know I could count the cells at the site of the original data but
counting the "traffic lights" would add to my summary info.
Thanks
JC

 

[ryguy7272]

Take a look at this:
(Counting Cells With A Specific Color)
http://www.cpearson.com/excel/colors.htm


You could try this macro too:
Sub countcolor()
Dim rng As Range
Dim cnt As Long

Set rng = Range("A1:E12")
cnt = 0

For Each cell In rng
If cell.Interior.ColorIndex = 3 Then
cnt = cnt + 1
End If
Next
MsgBox cnt
Range("G1").Value = cnt
End Sub



Regards,
Ryan--

 

[carlo]

Dear experts,
In my worksheets I have a "traffic light" system where cells with particular
values turn assigned cells in another location red, green or yellow using
conditional formatting. This provides me with a quick reference. My
question is can I count cells according to their colouring. Then I could say
I've got 20 cases complete, 10 cases in process, and 2 cases pending for
example. I know I could count the cells at the site of the original data but
counting the "traffic lights" would add to my summary info.
Thanks
JC

If the TrafficLight cells have distinguished values in them, then it
shouldn't be a problem
Let's say Red = 1, Yellow = 2, Green = 3, you can use the countif
function.
If they are Empty, you could use the same formula you have in
conditional formatting
to populate those cells!

Hth

Carlo

 

[jc132568]

Thank you, makes sense. Much appreciated.

If the TrafficLight cells have distinguished values in them, then it
shouldn't be a problem
Let's say Red = 1, Yellow = 2, Green = 3, you can use the countif
function.
If they are Empty, you could use the same formula you have in
conditional formatting
to populate those cells!

Hth

Carlo

 

[Gord Dibben]

ryguy

You posted the correct URL for Chip's site but did not read the site
introduction info before posting.

NOTE: These functions will not detect colors that are applied by Conditional
Formatting. They will read only the default colors of the cell and its text.
For information about returning colors in effect by conditional formatting, see
the Conditional Formatting Colors page.

See Chip's site and hit the link for more info and methods.

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


Gord Dibben MS Excel MVP