Conditional Formatting

T

Trip Levert

Help! I am trying to produce some conditional formatting
in code. The conditions are 1-4 and apply to each city
independently. The problem I am having is condition 3
where condition 1 must be met and the "or" in condition
3. Thanks!

Conditions:
Blue 1 Tracking Threshold -below 25 .
Green 2 Normal -below 10% of the 5 year average and last
year.
Yellow 3 Caution -above 10% of either the 5 year average
or last year.
Red 4 High -above 25% for both 5 year average and last
year.
Example:
ThisYear LastYear 5YAvg Vs.LYear Vs.5YAvg
City1 21.2 7.3 14.0 190% 51%(Blue)
City2 26.8 25.9 22.7 3% 18%(Yellow)
City3 28.0 29.6 25.4 0% 3%(Green)

There are six cities. Thanks Again!
 
D

David McRitchie

Hi ...........,
You can't have a condition checking an earlier condition
make that one your first condition. The other problem
is that Conditional Formatting is limited to 3 conditions.

Suggest you make your Red alert as a column i.e. Alert

Green C.F. 1 =AND($E2<0.1,$F2<0.1)
Blue C.F. 2 =AND($B2<25,$C2<25)
Yellow C.F. 3 =OR($E2>0.1,$F2>0.1)
[Alert] G2: =AND($E2>0.25,$F2>0.25,OR($B2>25,$C2>25))

Assumed you wanted to check both B and C for a low threshold

Enter your Conditional formula with the cells selected that you want colored
for the Red alert -- you've run out of your 3 wishes
so enter into G2 and fill down with fill handle

For 6 cities this is a lot of coloring

In order to check condition 2, condition 1 must be false
In order to check condition 3, condition 1 and 2 must be false

You can check your formulas by placing them on the spreadsheet

More on Conditional Formatting on my
http://www.mvps.org/dmcritchie/excel/condfmt.htm
 
Top