See other reply for prevention of copy/paste with code from John Walkenbach.

Alternative...........allow copy/paste but retain original formatting.

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Const WS_RANGE As String = "A:A"
Dim myValue
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 17 Dec 2009 03:36:02 -0800, mbp
wrote:

Hi
I've got a column of cells with validation.
If a user instead of typing values into the cells, copy/paste from another
cell, the origial validation settings are overwritten.
Is there any way to prevent the user to do this, otherwise validation is not
worth much.