Conditional formatting for more than 3 conditions in Excel 2002

[catherinemoore]

I'm trying to create conditional formatting to turn cell F12 different colours depending on the number in the cell. The rules are:

Red: >1.2
Orange: >1.1 to 1.2
Green: >0.9 to 1.1
Orange: 0.8 to 0.9
Red: <0.8

If I could have 5 conditions, it would be fairly simple, but as I am limited to 3 it means that I need a formula for each of red, orange and green. I've tried but can't seem to get it to work. Any ideas?

Thanks.

[mudraker]

If you need more than 3 conditional formattings thn try using a worksheet change event macro



'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

Please Login or Register  to view this content.

Note :-
In your discription you said > 0.9 to 1.1.
in the above code that used Case 0.9 To 1.1 without including the >
This is not a problem as this line of code is executed 1st
Case 0.8 To 0.9
which means that if the value of f12 is 0.9 the colour coding for the range 0.8 To 0.9 is used and the colour range for 0.9 to 1.1 is not run

The same structure also applies for 1.2

[arthurbr]

I'm trying to create conditional formatting to turn cell F12 different colours depending on the number in the cell. The rules are:

Red: >1.2
Orange: >1.1 to 1.2
Green: >0.9 to 1.1
Orange: 0.8 to 0.9
Red: <0.8

If I could have 5 conditions, it would be fairly simple, but as I am limited to 3 it means that I need a formula for each of red, orange and green. I've tried but can't seem to get it to work. Any ideas?

Thanks.

In fact in this case you only have 3 conditions, not 5:

I didn't test the values, you may want to adapt

[catherinemoore]

Thanks both. I have used arthurbr's approach to keep it consistent with the conditional formatting I have used elsewhere in the spreadsheet.