A little shorter and only one event.

Also doesn't wipe out CF

But only good for copy and paste. Crashes on Cut and Paste

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End Sub


Gord

On Sat, 7 Feb 2009 11:31:01 -0800, Shane Devenshire
wrote:

Hi,

There is nothing built into Excel to allow this. You could write a Change
event macro that returned the cell color to its value before you pasted into
it.

Something like this

Public myColor

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
myColor = Target.Interior.ColorIndex
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
Selection.Interior.ColorIndex = myColor
End If
End Sub