Painting Locked Cells

S

Shrikant

How to paint the locked cells automatically ? This will help me to quickly
identify the locked cells and guide the users to stay within the white region
only.
 
D

davesexcel

http://www.j-walk.com/ss/excel/tips/tip99.htm

you could color all the cells, then use the macro from the above sit
to select the unlocked cells then press no fill and it will clear.

If you have unlocked cells, then you protect your sheet, all the use
has to to is press the tab key when they make an entry and they will b
taken to the next unlocked cel
 
G

Gord Dibben

Sub LockedCells()
Dim wk As Worksheet
Dim cl As Range
For Each wk In Worksheets
For Each cl In wk.UsedRange
If cl.Locked = True Then
cl.Interior.ColorIndex = 36
End If
Next
Next
End Sub

OR a User Defined Function that you can employ in Conditonal Formatting.

Function Islocked(rng As Range)
If rng.Locked = True Then Islocked = True
End Function

Format>CF>Formula is: =Islocked(A1)


Gord Dibben MS Excel MVP
 
D

Dave Peterson

One more way if you're not using format|Conditional formatting.

Select the range you want to color (it can include locked and unlocked cells)
I used A1:x99

And A1 was my activecell.

Then I used:
format|conditional formatting
formula is:
=CELL("Protect",A1)

And gave it a nice pattern.
 
Top