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

"Rich Pasco" <richp1234@hotmail.com> wrote in message
news:OO1rLLOOGHA.2320@TK2MSFTNGP11.phx.gbl...
> 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

[Rich Pasco]

Thanks!

- Rich

Paul B wrote:

> 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
>
>

[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 wrote:
>
> 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

--

Dave Peterson

[Rich Pasco]

Thanks!

Dave Peterson wrote:

> 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 wrote:
>>
>> 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
>