Prevent Conditional Formatting Replication / Create Dynamic CF

[Jeffrey Mahoney]

When a user copies cells that have Conditional Formatting (CF), sometimes a new CF rule gets added with the same formatting as the previous rule. Let's say I have CF on a wide area A3:T400. I copy a row and paste below. I now have a new rule that covers only the new pasted row along with the original CF for the entire area.

Is there any way to prevent CF replication?

Is there any way to make the CF range dynamic?

 

[rlv01]

Not that someone smarter than me won't come along and tell us how to do that; but I've never found a way to prevent it. When there is a lot of copy/paste happening, CFs have a way of multiplying. But it can be cleaned up. My solution is to have a VBA routine that can be run to clean up the duplicates and/or extend the range if new rows have been added, but there are other ways. Google "Fix duplicated Conditional Formatting Rules" and you'll get a lot of hits.

 

[Jeffrey Mahoney]

Yup, I've had to write code to "HEAL" the CF. The problem that I've noticed is that I have to apply ALL the conditional formatting for a sheet to fix one of the CF rules. Meaning I have to code for all the potential rules. If I change one CF rule, then my code is outdated. It works fine if I only have one rule.