Due to creative use of drop downs and conditional formatting, I've got a lovely tool where customers use drop downs to define data sets, then (in 2003) would cut and paste the area where the drop downs existed to another tab within the same workbook to avoid using the dropdowns again.

I have conditional formatting on this that tells you what drop downs are legal to select, and others that will tell you if a drop down has been selected.

This is all fine and good - works like a charm. Until someone copies and pastes a defined data set to a new worksheet, at which point it does not overwrite the conditional formatting on the destination cells, it appends them to the existing rules.

Fast forward to three days of use with Excel 2007, and I've got workbooks with the same three conditional formatting rules appended about 400 times on some of these cells as users have blithely cut and pasted things. Which, when multiplied by 50 cells in the data block area, and 200 instances of the data block areas themselves in one workbook, results in crippling performance issues.

Is there a way to force Excel to overwrite existing conditional formats when pasting rather than concatenate new formats (or duplicates of existing formats) on the end?