Excel INDIRECT function and conditional formatting – highlighting a row

conditional formattingmicrosoft excelmicrosoft-excel-2007microsoft-excel-2010worksheet-function

I'm having an issue with conditional formatting using the INDIRECT function. I'm doing something similar to Using INDIRECT and AND/IF for conditional formatting , but the only answer there isn't working for me.

Basically, I want to highlight rows where B is not blank and F is blank. INDIRECT will work for ONE of the conditions, but 

= AND(INDIRECT("B"&ROW()) > 0, INDIRECT("F"&ROW()) = "")

does not work at all.

The answer in the question points to replacing the references with relative ones, so I'm thinking this should work:

= AND ($B2 > 0, $F2 = "")

But it does not, nor does ISBLANK($F@) or ISEMPTY($F2) (the cell contains a formula that sometimes will return "", I want the row highlighted in these cases but only when something is in column B).

Am I missing something about relative references? Why doesn't INDIRECT work with AND/OR?

Best Answer

I don't see any need for INDIRECT, "not blank" is <>"" so perhaps try

=AND($B2<>"",$F2="")

Related Question