Locking cells

G

Graham

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

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top