Pasting into a cell with conditional formatting

[pkeegs]

Users of my worksheet (Excel 2003) occasionally manually copy data from one
sheet into a sheet with conditional formatting. It requires them to
'paste>values'. Some users forget and simply 'paste' the data which deletes
the conditional formatting. Is there any way that I can protect the formats
so that if they 'paste', a dialogue box will indicate that they need to
'paste>values'?

 

[Wigi]

No, you can't (that is, in an easy way).

There are VBA-codes available that forbid copy-paste alltogether. A bit
drastic I assume.

I think that a solution should do the following (in pseudo-code): use a
hidden sheet that holds the conditional formatting. Then, use a
Worksheet_Change event to track the changing of (1 or more) cells. In the
event, you should check the CF for the corresponding cell on the hidden
sheet. If wiped away, restore it.

Or you write a general procedure that checks the CF on saving, or closing of
the file, or moving away from that sheet.