Condtional Formatting, Cell Upon Cell

[DOUG]

A co-worker wants to color-code a summation cell, so that if ALL of the cells
in that row are GREEN (i.e., in compliance or above the goal), then the
right-hand cell will be GREEN or "YES". If any cells are rated YELLOW, or
marginal, then the final cell will also be YELLOW or marginal. If any cells
are RED, then the final cell will coded RED or say "NO". (We can use
Conditional Formatting to color the result cells after-the-fact if need be.
She would prefer to use stoplight symbols, GREEN-YELLOW-RED in the right-hand
column and to sort by them if possible). Is this possible - (with a
reasonable amount of effort)? So far, I have tested IF statements, AND
statements and LOOKUP statements without success.
DOUG

 

[Luke M]

For this example, lets presume your actually looking for the words "red"
"green" and "yellow"

Condition 1:
=COUNTIF(A2:G2,"red")>0
format red

Condition 2:
=COUNTIF(A2:G2,"yellow")>0

condition 3:
=COUNTIF(A2:G2,"green")=7
format green (where 7 is equal to number of cells you are checking)

Obviously, you can change the COUNTIF function to depend on your actual
criteria.
For the wording of the cell, simply combine these int
=IF(COUNTIF(A2:G2,"red")>0,"No",IF(COUNTIF(A2:G2,"yellow")>0,"Marginal",IF(COUNTIF(A2:G2,"green")=7,"Yes","Did not meet any crtieria")))

 

[DOUG]

Luke: I am using actual colors and not words. But, the conditional colors
indicate compliance with another cell value on the same spreadsheet. So, I
need an IF statement of some kind to say "If any one of this range of cells
scores in the yellow range of values, color this cell yellow. If any cell is
in the red range of values 0 i.e., below the cut-off for yellow values, color
this cell red". It should be do-able if the IF statement incorporates the
numerical threhold values from elsewhere in the spreadsheet. (The syntax is
still a little bit off. Suggestions are welcome).

Thanks,
DOUG

 

[DOUG]

Luke: In other words, I am trying to say, "If ANY cell in this range is
greater than this, then..."

DOUG