Excel INDIRECT function and conditional formatting - highlighting a row

Question

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?

Solution 1

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

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

Solution 2

Your issue could be where you start to apply the conditional formatting. The answer with = AND ($B2 > 0, $F2 = "") assumes you are applying your conditional formatting row 2 of any column.

For example if you are applying your conditional formatting rule to an entire column the first row that the rule is applied needs to be the row number in your formula. You would simply highlight the entire column then type = AND ($B1> 0, $F1 = "")

If you are applying the formatting to G5 through G7 this is what you would input. = AND ($B5 > 0, $F5 = "")

Relative references will start where your formula starts then proceed to copy itself across the range you are applying the rule to. That's why the $ isn't present on the number. So the rule can move down with your range.