Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
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($E20.1,$F20.1) [Alert] G2: =AND($E20.25,$F20.25,OR($B225,$C225)) 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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Trip Levert" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |