I tried searching but for some reason I couldn't find anything even though I'm sure other people have run across this problem.

Let's say I select column A:A and apply conditional formatting to detect duplicates. Through the course of my workbook's life, that range of A:A will change due to adding/deleting cells/rows or maybe copying+pasting.

Could someone explain exactly when the conditional formatting range changes and tell me what to do about it? It seems arbitrary to me: sometimes I add a row and nothing happens, and sometimes I add a row and and the range will change to something like $A$1:$A$389,$A$391:$A$30838,$A$30840:$A$63356 (I just made that up, but my point is it changes to something really obnoxious!)

Ideally I want to be able to apply conditional formatting to a table and have it dynamically change with the table as you add or delete rows and copy and paste. I don't want anyway to inadvertently remove the conditional formatting. How do I do this? I even tried using VBA to automatically reapply the conditional formatting whenever any cell in the range changed (which worked), but it removed the ability to Undo.

Summary: I want to apply conditional formatting to one column of my table. Often, the conditional formatting range will change inadvertently so it no longer covers the entire column of my table. This probably happens through adding/deleting rows and copying/pasting. How do I make my conditional formatting STAY?

Thanks!