Combining Conditional Formats

**demesne** · 04-26-2013, 04:54 AM

Hi

I have several lists of numbers ranging in value from -20 to +20. I currently have six conditional formats setup to colour the text based on its value.

ie:

<= -14.5 (stop if true. text blue)
<= -9.5 (stop if true. text red)
<= -4.5 (stop if true. text orange)
>= 14.5 (stop if true. text blue)
>= 9.5 (stop if true. text red)
>= 4.5 (stop if true. text orange)

With having six conditional formats I find the workbook is not fully compatible with earlier versions. My question is can I combine these statements in a formula to give me three conditional formats?

Something like: <= -14.5 OR >= 14.5 (stop if true. text blue)

I've tried and failed.

Thanks
Andrew

**benishiryo** · 04-26-2013, 05:17 AM

hi Andrew, welcome to the forum. i forgot if the earlier versions can prioritise the rules. you can try putting in this order.
select the range you want to apply to (say B1:B10)
in Excel 2007 & above, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
in Excel 2003, go to Format -> Conditional Formatting -> Formula is:
=ABS(B1)>=14.5
=ABS(B1)>=9.5
=ABS(B1)>=4.5

if it fails, then:
=ABS(B1)>=14.5
=AND(ABS(B1)>=9.5,ABS(B1)<14.5)
=AND(ABS(B1)>=4.5,ABS(B1)<9.5)

**Neil_** · 04-26-2013, 05:19 AM

Or is a function, so you need to treat it like one. try =OR(A2<=-14.5,A2>=14.5)

**demesne** · 04-26-2013, 06:05 AM

Thanks benishiryo I see what you mean but I was looking for something similar to what Neil suggested.

**demesne** · 04-26-2013, 06:11 AM

Thanks Neil. I had tried OR but I was trying to include the range of cells in the formula rather than giving it one cell ref (as you did) and letting Excel do the work.

Sometimes I can’t see the forest for the trees.