I am trying to use conditional formatting to highlight a cell if it is blank and a cell in the first column of the same row is not blank. For example, if the conditional formatting is for cell D4, I want D4 to be highlighted if D4 is blank and A4 is not blank. If the conditional formatting is for cell H36, I want H36 to be highlighted if H36 is blank and A36 is not blank. Here is what my rule looks like (spaces added only here, for clarity):
=AND(NOT (ISBLANK ( INDIRECT(ADDRESS (ROW(), 1 )) ) ),(ISBLANK (INDIRECT (ADDRESS (ROW(),COLUMN()))))
I know that this is a bit of a doozy, so here is the breakdown:
- The AND function is straightforward--I want the highlight to only occur when both checks return True.
- The NOT (ISBLANK) is pretty self-explanatory--I am checking to see if the cell has some value/formula in it.
- The ADDRESS function, as I understand it, returns the address for a cell, converting the row and column numbers. I want to keep the same row for the check, so I will take the row of the cell being checked. The column number is a constant 1, because I want to check the first column (i.e., column A).
- The ISBLANK function at the end is a simple check on the cell subject to conditional formatting, checking to see if it is blank. My range for this conditional formatting is B2 to K51.
Unfortunately, Microsoft Excel has a problem with this formula. The parentheses look good to me, I think I satisfied all of the required arguments, and the return values appear to be appropriate throughout, so I am not sure where the problem lies. I even tried the formula with B2:K51 as the ROW() argument, but no such luck. Could someone please explain why Excel is rejecting my conditional formatting formula?
