Conditional Formatting Based on Colors in a Horz Range of Cells

[DOUG ECKERT]

I have a co-worker who wants to insert a formula to say "If all of the cells
in a range are green, then this cell is green. If some cells are yellow,
then this cell is yellow. If any cells are red, this cell is red".

??

DOUG

 

[Luke M]

Sadly, you can't write a formula based on colors. (Well, without using VBA)
However, is there some function/conditional format making the other cells
these colors? If so, you/we can create a formula based on that.

 

[DOUG ECKERT]

Luke: A VBA-based answer would be nice. However...

There is a cell (N2) containing the goal value of "80" (percent). If I knew
how to include all of the cells in the range in the "IF" statement, I could
say "If all of these cells are greater than or equal to cell N2, then color
this cell green - (or, failing that, make this cell say "YES" and then
conditionally format all of the "YES" cells as green). They prefer a green
stoplight to green fill, by the way.

There is a second cell for the "Yellow" cutoff, too.

DOUG

 

[DOUG ECKERT]

'Anybody?

DOUG

 

[Gord Dibben]

How did the cells get to be green or yellow?

If by Conditional Formatting, use the CF criteria in your formula.

If manually colored you will need to add a User Defined Function to
ascertain the color.

See Chip Pearson's site for some code.

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


Gord Dibben MS Excel MVP

 

[DOUG ECKERT]

Gord: I do not know how to put CF in a formula, beyond using the CF tool.

What this person wants to do is to look at all of the compliant cells in a
row. If ALL cells are compliant, the overall grade is a GREEN stoplight - as
in, "Good to Go". If ANY cells are rated marginal - with scores in the
marginal or "YELLOW" range - the summary field would show a YELLOW stoplight.
If rated below the YELLOW range, it would show a RED stoplight. Any RED
cells would generate a RED stoplight in the summary field. (Compliance will
be noted as above a certain threshold equals YES, or in some cases YES equals
Yes, based on checklist questions. The individual cells contain numbers to
be compared to a certain goal, which is reflected in another cell, at least
in most cases). In short, either ALL Comply, SOME Comply (with no RED
scores), or NONE comply (if ANY RED scores). So far, I have tried the IF,
AND and LOOKUP functions, to great frustration. Cell colors are based on
Conditional Formatting, but the formula could reference the cell containing
the compliance goal, at least for generating the GREEN stoplight. Adding the
YELLOW and RED criteria is a great deal more complicated.

"IF" worked, until I ran out of nested functions to use.

 

[Gord Dibben]

I don't know where to start with this.

But what I'm going to do for a while is deal with 25cm of snow that dumped
in the past 4 hours...............and more coming all night.

I vill be baawwwk


Gord

 

[Dave Peterson]

I'm not Gord (and he's thankful everyday for that!), but how about sharing some
more info.

What is the cell that contains the threshold value?

What are the columns in each row that contain the value to be compared?

When you're doing the conditional formatting, how do you know if the cell should
be yellow or red? I would assume that if the value in the cell is greater than
or equal to the threshold, then it's marked green. But how do you determine Red
from Yellow?

 

[DOUG ECKERT]

Dave: The colors are all determined by whether the cells meet or exceed the
cut points, or goals, which are contained in other cells. It is set up using
the standard conditional formatting features.

DOUG

 

[Dave Peterson]

Can you describe the cells that get shaded and share the rules for each of those
cells?

Right now, I don't see how anyone can take a guess.

 

[michael0511]

This is actually pretty easy, because it's not actually conditional
formatting based on colors, it's conditional formatting to summarize the
results of other cells that have conditional formatting applied to them.

Either base your "Stoplight cell" colors on ALL the original conditions that
created the individual colors, but you seem to have tried that.

You could create a separate (perhaps hidden) "indicator cell" that applies
all the necessary tests to the individual values in the row to come up with
whether the row is green, yellow or red, then format based on that.

How do you set up the "indicator cell"? One possible way is by using IF,
AND, and OR statements to build a formula that results in 0 if red, 1 if
yellow, and 2 if green. There are a lot more options. With more specifics,
perhaps I could build an example.

It seems you're saying the function is too complex for a single cell, so
test the row to see if it meets the yellow criteria in one cell, then if it
meets the green in the next.

I would probably go back pair the original value cells with hidden or
off-screen individual indicator cells that evaluate to "True" or "False" to
get the original color formatting for each, then build my "row stoplight"
directly from the values in those same cells.

I can't imagine how or why a LOOKUP would be useful here, and wouldn't use
it.