Cell Protection Color

[Guest]

I have a large spreadsheet that has alot of single cells that need to be
locked. Is there an easy way to see the locked/unlocked status of the cells
on a spreadsheet? Maybe by having locked cells shaded? Thank you for any help
anyone can give.

 

[Dave Peterson]

Are you using Format|conditional Formatting?

If no, you could use that to shade the locked cells.

Select your range (assuming A1 is the activecell in that selection)
format|conditional formatting
formula is:
=CELL("protect",A1)

Format to your liking.

======
I use this:
Tools|customize|commands tab|Format category
Near the bottom of that list of icons/commands is: Lock Cell.
(drag it to your favorite toolbar)

It's useful for toggling the lockedness of a cell. But you can select a cell,
look at that icon. If it's depressed, the cell is locked. If it's not
depressed (happy as a clam???), then the cell is unlocked.

 

[Gord Dibben]

Kmagg

Dave's CF is a simple method and works well, but uses up one of the CF
conditions for those cells.

You could also use a macro to color the locked cells.

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

tempR.Select
tempR.Interior.ColorIndex = 3 'which is red
End Sub


Gord Dibben Excel MVP

 

[Guest]

COOL!!! Works perfectly. I just went into the Edit Macro, posted your code
and all the locked cells turned red. Thanks so much for the excellent advice.

Kerry

 

[Gord Dibben]

Thanks for the feedback.

Glad to help.

Gord

COOL!!! Works perfectly. I just went into the Edit Macro, posted your code
and all the locked cells turned red. Thanks so much for the excellent advice.

Kerry