Conditional format on 2 criteria - excel 2010 in compatibility mode to 2003

**greyscale** · 08-16-2012, 02:55 AM

Hi,

I am struggling with this new way of conditional formatting in excel 2010. The way you have to use absolute values even when the condition applies to multiple rows really confuses me, especially when it comes to using fomat painter to copy formatting. The attached document is a stripped down version of my real document. I am trying to make the font go bold if 2 conditions are met:
If A=Client and D < 6.5%
If A = NB and D < 15%

The data for A and D in my real document are not actual values but are results of formulas. Not sure if that makes a difference.
Also, although this is an excel 2010 doc, any formulas need to be compatible with 2003.

Can anyone help?

**outofthehat** · 08-16-2012, 03:08 AM

Conditional Formatting > New Rule > Use a formula to determine which cells to format > Format values where this formula is true
=OR(AND(A2="Client",D2<6.5%),AND(A2="NB",D2<15%))

---------- Post added at 08:06 AM ---------- Previous post was at 08:04 AM ----------

Probably should have said "Highlight D2:D9" first

You don't need the IF statement in your conditional format rule - all you have to do is write a formula that will be true or false, and formatting is applied when it's true. The one above uses ANDs to build each condition, then an OR to apply it when one or other is true.[COLOR="Silver"]

**greyscale** · 08-16-2012, 07:31 PM

Ah, thank you. The IF was tripping me up and I thought I had to use absolutes.. I find the conditional formatting realy confusing because it seems that it is still referring to A2 for all rows but it's obviously not because it works. THanks.