Conditional Formatting within a Table


I have two rather simple conditional formatting rules defined that apply to the contents of a table. Over time, as rows are added, deleted, or moved, the rules get chopped up. So although I started with just two rules, I end up with dozens of duplicate rules, each with a different (and sometimes overlapping) 'applies to' range. I'm sure that the problem starts with Excel changing my column reference (clicking on the column label, expecting to see something like TableName[ColHeading]) into standard references ($a4:$47 for example).


I can't figure out how to make the appropriate column reference inside the conditional format 'applies to' box, that will dynamically update as the table changes, hoping then that Excel will not chop the reference up into smaller pieces.


Any thoughts?

2 Replies
best response confirmed by JBF_54 (Contributor)



In the past I've noticed that same phenomenon. I think you answered your own question with this:

Over time, as rows are added, deleted, or moved, the rules get chopped up.


I've learned to be very consistent, when I add rows, to put them in the center of the table, and then sort according to whatever actual sequence I want. Adding outside of the range of rows initially specified can be problematic. Deleting doesn't affect those rules. "Moving" on the other hand....kind of depends on what you mean. But as long as the rules apply equally to all cells (i.e., all rows) in a column, and I'm careful about how a add or delete, it's not been a continuing issue.



Thanks for the insight. It helps a little. Since many of the table modifications were accomplished through macros, I'm thinking I'll change the logic to add records "1 from the bottom (or top)" rather than at the bottom or top.


I'll call this question answered, but if someone has more advice, please let me kmow.