Conditional formatting - multiple criteria

**bobbie_ch** · 04-02-2013, 08:03 AM

Hi,

Need some help. I am trying to format text using conditional formatting. This is what I want to do:
If column H = "Implemented" and Column F < 90, change color to Yellow.

Using the formula
=AND(INDIRECT("H"&ROW())="Implemented",INDIRECT("F"&ROW())<90)
which is not working.

Any help is appreciated.

Regards,
Bobbie

Tracking.xlsx

**Fotis1991** · 04-02-2013, 08:19 AM

Try this.

=AND($F5<90,$H5="implemented")

**bobbie_ch** · 04-02-2013, 08:28 AM

Wow, thanks. That was quick and it works great. Just for my understanding, what does $F5 represent here ? The range of cells between F5 and F10,000 (or whatever the upper limit is?

**benishiryo** · 04-02-2013, 08:45 AM

hi bobbie_ch, welcome to the forum. i would definitely go with Fotis' method, but i'm extremely curious what went wrong with your formula. i normally avoid using INDIRECT because it's a volatile formula (and not necessary to use in this case). the thing is, it should work still. if i separate the logic like this, it works well individually.
=INDIRECT("F"&ROW())<90
=INDIRECT("H"&ROW())="Implemented"

but somehow, it just wouldnt work when combined in Conditional formatting. seems like it doesnt work well with AND with ROW(). if you could, please do not mark the thread as "Solved" for 1 or 2 days. just for my curiosity's sake.

thanks

Wow, thanks. That was quick and it works great. Just for my understanding, what does $F5 represent here ? The range of cells between F5 and F10,000 (or whatever the upper limit is?

the Conditional Formatting only allows a single formula for a wide range. so try placing Fotis' formula in T5 & copy down. that's what it does internally. and that's why it's important to select from F5 downwards. if you select F100 to F5, it's as though you are placing the formula in T100 & copy up. HUGE difference. the dollar signs do not matter in this case since you are applying only to a single column. an eg is this formula that you apply across columns:
=INDIRECT("H"&ROW())="Implemented"

it can be:
=$H5="Implemented"

so try it again in T5. if you copy to the right, it will still be comparing column H. if you remove the dollar, it will be comparing if I5 is "Implemented"

**bobbie_ch** · 04-05-2013, 12:08 AM

Thanks again for your help. Marking it as solved.

**daddylonglegs** · 04-05-2013, 08:46 AM

I don't know exactly why it doesn't work but I think it's linked to the fact that ROW() function returns an array, even if only a single value, e.g. it returns {5} rather than 5. Some functions seem to have problems processing that array.

A workaround would be to put some other function around ROW to convert to a number, e.g. MAX or SUM like this

=AND(INDIRECT("H"&SUM(ROW()))="Implemented",INDIRECT("F"&SUM(ROW()))<90)

That version should work in conditional formatting (although I wouldn't recommend it as there are better solutions like the one suggested by Fotis)