This sheet event code will retain the formatting of the copied-to cells.

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

On Thu, 30 Aug 2007 20:14:04 -0700, Carmi
wrote:

Hi Dave - thanks for your reply.

I did not realise that this was standard functionality... I do now.

I have one further question that I am hoping you can help with. The
protection works correctly when the user keys the data into the sheet (i.e
can not change the formatting of the cell). However, if they cut and paste
it from another sheet the formatting from the source sheet is pulled across.
Is there a way to stop this from happening without using Paste Special? I
want the destination formatting to take precedence.


"Dave Peterson" wrote:

Lock the cells that should not have their values be changed
Unlock the cells that are ok to have their values changed
(Format|Cells|protection tab)

Then protect the worksheet.
(Tools|Protection|protect sheet)

What you asked for is the default behavior for a protected sheet. If you wanted
to allow users to format those unlocked cells, you can use the options in the
Tool|Protection|protect sheet dialog.

Note that worksheet protection is easily broken by anyone who's really
interested.

Carmi wrote:

MS Excel 2003

Is it possible to protect a worksheet to accomplish the following;

Protect Column A so users can not edit
Protect Columns B:D so users can enter data but not change the format

--

Dave Peterson