How do I show protected cells in an EXCEL worksheet

[Guest]

Once I have protected a sheet leaving unprotected cell to allow users chsnge
or input data, users of this sheet do not know which cells they can change
(unprotected) unless they try to click on every cell and are allowed to
change the data in it.
My question is: is there a way to display protected or unprotected sheet and
if there is how to do it; and whether we can control the movement of the
cursor in a protected sheet.

 

[Guest]

Try:

Sub ColorLocked()
Dim cell As Range
Dim ThisWs As Worksheet
Set ThisWs = ActiveSheet
ThisWs.Unprotect Password:="thisismypassword"
For Each cell In ThisWs.UsedRange
With cell
If .Locked Then
.Locked = False
.Interior.ColorIndex = 6
.Locked = True
End If
End With
Next
ThisWs.Protect Password:="thisismypassword"
End Sub

---
To use, press ALT+F11, then go to Insert > Module, and paste in the code
above. From there press ALT+Q to close the VBE and run the macro from Tools >
Macro > Macros.

HTH
Jason
Atlanta, GA

 

[Bob Phillips]

This is a re-print of a previous post of mine that will highlight locked
cells

Sub LockedCells()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Locked Then
With cell
With .Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
.BorderAround LineStyle:=xlDashDotDot, Weight:=xlThick,
ColorIndex:=5
End With
End If
Next cell
End Sub

Change
If cell.Locked Then
to
If Not cell.Locked Then



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Also, using the Tab key to navigate within the sheet will take the user to
only the unlocked cells.