[Graham]

how do I lock the fill color in a cell but still have the option to copy
data to it.

[Gord Dibben]

When you paste, paste specialvalues.


Gord Dibben MS Excel MVP

On Sat, 7 Feb 2009 11:06:01 -0800, Graham
wrote:


how do I lock the fill color in a cell but still have the option to copy
data to it.

[Shane Devenshire[_2_]]

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



--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Graham" wrote:


how do I lock the fill color in a cell but still have the option to copy
data to it.

[Gord Dibben]

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