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 
CodePudding user response：
ISBLANK() works for me. But you can try-
=INDIRECT("Table1[@Col2]")=""
CodePudding user response：
Your issue is that your rule is using an absolute cell reference (ie., $B$2) rather than relative reference (ie., B2), so, anywhere you copy that rule will still be looking at B2.
It's confusing (and a little annoying) partially because Excel defaults to absolute references in this case. Also, if you try to change the rule to remove the $'s, you might notice that the arrow keys don't do what you might expect. (It's often easier to make that change by highlighting the cell reference and hitting F4 one or more times.)
I've got in the habit of taking steps in a certain to apply conditional formatting to several cells like you're trying to do.
I usually would select the first cell, add the new rule to that cell, then go back in and manually remove the $'s from the rule, then copy the cell, and Paste Formats everywhere else I want it. I find this works better than trying to apply the rules to multiple cells at once.
More info about relative, absolute, and mixed references.