Lock formatting

[Guest]

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

 

[Gord Dibben]

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

 

[hfazal]

Not sure how to apply this?

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

 

[Gord Dibben]

Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module.


Gord