Protecting cell formatting
Private Sub Worksheet_Change(ByVal Target As Range)
'retain target cell formatting when a cell is copied over
.EnableEvents = False
myValue = Target.Value
Target = myValue
.EnableEvents = True
Gord Dibben MS Excel MVP
On Thu, 26 Jun 2008 11:19:23 -0700, Sam Hills wrote:
I am having a similar problem. I can protect cells so that the user cannot select them, and unprotect the data-entry cells so that the user can enter data. "Format cells" is unchecked, so the "Format cells" option in the right-click menu is greyed out, and the "Cells" option on the "Format" menu is likewise greyed out. However, if the user copies one data-entry cell and pastes it into another, the formatting is pasted too.
To demonstrate this:
1. Format column A as text, column B as date and column C as number. On the Protection tab, uncheck "locked".
2. Protect the sheet. Make sure "Format Cells" is unchecked.
3. Select a cell in column A and copy it to the clipboard with <Ctrl-C.
4. Paste that cell into cells B1 and C1.
5. Turn sheet protection off.
6. Right-click on cell B1 and select "Format cells". The format has been changed to Text. Likewise for C1.
How can I protect cells so that pasting another cell will only paste the source cell's value but not its formatting?