Locking cells

[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 special>values.


Gord Dibben MS Excel MVP

 

[Shane Devenshire]

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

 

[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