Indeed this is a great workaround for when the stoplights are set based on
numbers and may be the way we end up as counting colours and applying a
weighting ( a second set of columns) gets me to the rolled up stoplight. I
will only need to reconfigure the individual lits base don numbers rather
than % and other formula as they are currently done. The ActiveCondition is
more elegant, but above my VBA capability...Thanks for this hint I am
applying it now.

"Joel" wrote:

I don't think counting the colors in the conditional formula cells is the
correct way of attacking the problem. The conditions that color the cells
can be duplicate using formulas in an auxilary cell.

I fyou have three conditions in the contional format

Red : A1 less than 25
blue : A1 25 to 50
Green: A1 50 to 100


You can put in another cell

=LOOKUP(A1,{0,25,50;"Red","Blue","Green"})

then you can count the colors in a different column.


"raking08" wrote:

Thanks Joel, This looks like the right approach, I was only hoping for a non
VBA solution as I am not a programmer. The active condition is exactly what I
need ( not the trick is to get it to work to return a sum for a stack of
cells..

"Joel" wrote:

You need a complcated macro to determine the colors that are being displyed
from a conditional formated cell. See webpage below for more detailed
explanation and a sample of the code you would need.

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

"raking08" wrote:

Is there a way to assign a specific value to a "stoplight conditions (
without having to write a complicated "if" statement in order to summarize a
group of individual rules into a single "stoplight" when the actual data does
not normalize easily. ?

in other words, is there something behind the scenes in conditional
formating that "knows" that a cell is red, yellow or green that can be
combined into in new summary rules ?

I am rolling up a 100 query dashboard into small groups