Excel protecting formatting

[Rob]

Hi All

How can I prevent cell formats from being removed or
being overwritten when data is cut or pasted from another
cell.

I do not wish to rely on users having to use the "Paste
Special" option.

Many thanks

 

[Dave Peterson]

Maybe you could just intercept the change. This worked in light testing.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.