Refreshing formatting

[ChemistB]

This thread is a continuation of this one: http://www.excelforum.com/showthread.php?t=631146

Here's what I have:
A workbook (single sheet) which is protected with open cells for people to either manually enter data or copy and paste from another source (typically another Excel sheet).

Here's what I want:
I want for the cell formatting to be refreshed after any input.

The Problem: I had thought to keep the original formatting on a hidden sheet and then copy and paste it after entry. The VBA I've come up with so far are event driven and clear the clipboard before the analyst has a chance to paste what they want.

Other Question: It's been my experience, that when copying and pasting from a separate workbook, the conditional formatting and validation are not affected. True?

[royUK]

Have you tried using PasteSpecial >- Values?

[ChemistB]

Yes, however, this would be a company spreadsheet and I can't trust everyone to remember to Copy Paste Special even though that's the way they will be trained.

ChemistB