Conditional formatting Rule Type 'Use a formula to determine which cells to format' in a table looks at the value in the row the rule was determined

Question

I have created a table (a listObject in VBA-terms) with one row of data in Excel. Before adding rows to the table, I add conditional formatting to the first row, in the hopes that new rows will inherit the conditional formatting. I see that, depending on the conditional formatting Rule Type I use, the formatting behaves a bit different for new rows. Let me illustrate with an example what I mean.

In the example below, I have added conditional formatting for B2 - when the cell is empty, use orange color fill. Afterwards, when adding rows to the table (see row 3 and 4), the conditional formatting is kept and, per row, it only matters what is found in the column B of that row.

However, when I use the Rule Type 'Use a formula to determine which cells to format', it's always the value in the first row of data (i.e. row 2) that seems to matter. Take for instance the rule below where Col1 formatting changes text to bold and red when Col2 is empty. This is of course because $B$2 is used in the formula. How can I make sure that, when we are in row 3, the rule will take into account the value of B3 and not B2? I browsed StackOverflow for this and found the interesting post Use a formula based on a table to determine which cells to format. However, while it seems to work for rules like =INDIRECT("Table1[@Col2]")="a", it doesn't work when I try things like =ISBLANK(INDIRECT("Table1[@Col2]")) - it doesn't do any formatting. Can one make it work for the latter?

Answer 1

ISBLANK() works for me. But you can try-

=INDIRECT("Table1[@Col2]")=""