Condtional Formatting

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!

I ask my question in general, but I'll give you one specific example. Before I get into it, the answer to your question is that I use conditional formatting to check for duplicates (the simple conditional formatting preset that highlights in pink)

I use Excel 2007. I have this table (i.e. an official table object) that's roughly 10 columns and 1500 rows. There are no formulas; it's just a database where I type in entries. It has three columns with conditional formatting (Column 3: check for duplicates, Column 9: check for duplicates, Column 10: Check for strings that aren't "USA")

Whenever I add a new entry, I just start typing in the row below the last row of the table. This automatically adds a new row to the table. When I do this, the CF in Column 3 will not update (i.e. if I add 100 new rows, the CF range will be 100 rows short). HOWEVER, Column 9 and Column 10's CF are fine and automatically extend to the last row in the table.

This is frustrating because it's seemingly arbitrary. There's no reason why Column 3 should be broken while the other two work. Again, I ask: can anyone explain when a conditional formatting range is static and when it's dynamic and how to fix it?

Thanks again!

Well, mystery solved (I think). It seems like the only time when it screws up is when you paste. What ended up happening is that I would copy email addresses from gmail and paste into column 3 to make sure there were no duplicates. I guess that text contained formatting even though it didn't look like it did, so it would erase the conditional formatting.

BUT, I'm still looking for some advice. Is there anyway I can get around this problem? I would like to be able to paste into a range without removing its conditional formatting. I know paste as values is an option, but I would still like to know of any alternatives.

Thanks