Shading protected cells

[Rich Pasco]

Using Excel 2003 on Windows XP, I would like to see at a glance which
cells are Locked and which are not, by automatically shading the
background of just the Locked cells. How can I do that?

If that's too hard, is there any other way I can quickly tell which
cells are Locked, for example looking at a status line as I move the
cursor over the cells? or any other way that's faster than opening
Format/Cells/Protection on each cell one at a time?

- Rich

 

[Paul B]

Rich, here is one way with a macro,

Sub Highlight_Locked_Cells()
'will color all locked cells in the sheet
Dim Cel As Range
For Each Cel In ActiveSheet.UsedRange.Cells
If Cel.Interior.ColorIndex = 46 Then Cel.Interior.ColorIndex = 0
Next
For Each Cel In ActiveSheet.UsedRange.Cells
If Cel.Locked = True Then Cel.Interior.ColorIndex = 46
Next
Exit Sub
End Sub

Sub Remove_Highlight_Locked_Cells()
For Each Cel In ActiveSheet.UsedRange.Cells
If Cel.Interior.ColorIndex = 46 Then Cel.Interior.ColorIndex = 0
Next
Exit Sub
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Dave Peterson]

I added an icon to a toolbar
tools|customize|commands tab|format category
look for the "lock cell" icon. In xl2003, it looks like a padlock.

Then drag it to your favorite toolbar.

You can select a cell and look at that icon. If it's depressed, the cell is
locked.


===
Another way if you're not using format|conditional formatting, you could select
the cells (all of them???) and with A1 the the activecell:

Format|conditional formatting
formula is: =cell("protect",a1)
Give it a nice format.

 

[Rich Pasco]

Thanks!

- Rich

 

[Rich Pasco]

Thanks!

I added an icon to a toolbar
tools|customize|commands tab|format category
look for the "lock cell" icon. In xl2003, it looks like a padlock.

Then drag it to your favorite toolbar.

You can select a cell and look at that icon. If it's depressed, the cell is
locked.


===
Another way if you're not using format|conditional formatting, you could select
the cells (all of them???) and with A1 the the activecell:

Format|conditional formatting
formula is: =cell("protect",a1)
Give it a nice format.