Copy and paste only........

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is pasted over
On Error GoTo endit
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
End With
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 25 Oct 2007 15:54:00 -0700, hfazal
wrote:

Hi, how can I set up a cell/group of cells so that the data in them can be
changes but the formatting cannot. For instance if I put the number
formatting as a percentage to 5 decimal places, then even if someone
copy/pastes something from a different cell that is not in that format, the
cell will take the new input without taking the formatting...

Thanks
HF