Change cell colour if.....

[Guest]

Looking to have certain cells that are unlocked, to change to
yellow background
- Else, stay or change to standard white background.

To check / apply to complete work area of worksheet.

This should allow me to run macro whenever I want, in the
event that cell locking or unlocking was changed.

Assuming this would be a macro, I would set this up to to
operate from a button on my custom personal macro toolbar.

John F. Scholten

 

[Frank Kabel]

Hi
why don't you check in a macro directly the locked state of the cells?
or even unprotect the sheet before executing the code?

 

[Gord Dibben]

Just for info to start.

The default background for cells is "no color" not "white". Format to "white"
and your gridlines will disappear.

Second........code from Bob Flanagan of http://www.add-ins.com/pages.htm

Sub Locked_Cells()
Dim Cell As Range, tempR As Range, rangeToCheck As Range
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Cell.Locked Then
If tempR Is Nothing Then
Set tempR = Cell
Else
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
If tempR Is Nothing Then
MsgBox "There are no Locked cells " & _
"in the selected range."
End
End If
'select qualifying cells
'tempR.Select
tempR.Interior.ColorIndex = 6 'yellow
End Sub

Sub UnLocked_Cells()
Dim Cell As Range, tempR As Range, rangeToCheck As Range
Cells.Select
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not Cell.Locked Then
If tempR Is Nothing Then
Set tempR = Cell
Else
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If
'select qualifying cells
'tempR.Select
tempR.Interior.ColorIndex = 3 'red
End Sub

Gord Dibben Excel MVP

 

[Guest]

Hi:
Once I added Cells.Select to your code and changed colour to 2 'blank,
it worked great
I changed Bob's to 6 'yellow and it works great

Now all I need is to somehow "join" the two, so I can put it on a button.

Thanks for your help.

 

[Gord Dibben]

Sorry about the Cells.Select

I should have mentioned Hit CRTL + A and run macro....

or added the Cells.Select line at start of code.

Why join the two? Don't understand the need.

Gord

 

[Guest]

If, at any time after running the "Y" macro, I have to make any
changes to any of the cells so that the lock / unlock status changes,
Re-running the macro, does not change yellow cells to blank, although
the "new" unlocked cells do change to yellow.

I have tried various ways of having the two supplied macros in one
module, but I don't know enough yet how to do this, so my efforts
are getting me all kinds of various warnings and instructions.
(first one is of coarse the duplication of range instructions, etc)

Thanks

 

[Gord Dibben]

John

In the Unlocked Sub you could first reset all cells to "no color".

Dim Cell As Range, tempR As Range, rangeToCheck As Range
Cells.Select
''add this next line
Cells.Interior.ColorIndex = -4142

Then the rest of the macro runs to set the unlocked cells color to yellow.

Don't use colorindex of 2. This is white, which will wipe out gridlines.

The default "no color" is -4142

Gord