Identifing Locked Cells

[Steve Klenner]

I have a fairly complex spreadsheet that requires salespeople to fill in
info. Many cells are locked from being changed. Is there a way to create a
macro that looks a current sheet and will change the cell color if cell is
locked (or unlocked)? Right now I review by going from cell, to, cell, to
cell.....

Any suggestions is MUCH appreciated....
Steve

[Paul B]

Steve, here is one way,

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


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



"Steve Klenner" <steve_klenner@bsbcom.com> wrote in message
news:uA$mJfsmGHA.2160@TK2MSFTNGP04.phx.gbl...
>I have a fairly complex spreadsheet that requires salespeople to fill in
>info. Many cells are locked from being changed. Is there a way to create
>a macro that looks a current sheet and will change the cell color if cell
>is locked (or unlocked)? Right now I review by going from cell, to, cell,
>to cell.....
>
> Any suggestions is MUCH appreciated....
> Steve
>
>
>

[Mark Driscol]

Rather than a macro, can you set up a format Style and apply it to
locked cells? You could set up the cell color using the Style you
define.

Mark


Steve Klenner wrote:
> I have a fairly complex spreadsheet that requires salespeople to fill in
> info. Many cells are locked from being changed. Is there a way to create a
> macro that looks a current sheet and will change the cell color if cell is
> locked (or unlocked)? Right now I review by going from cell, to, cell, to
> cell.....
>
> Any suggestions is MUCH appreciated....
> Steve

[Steve Klenner]

Paul -

The Macro Worked like a charm.

Steve


"Steve Klenner" <steve_klenner@bsbcom.com> wrote in message
news:uA$mJfsmGHA.2160@TK2MSFTNGP04.phx.gbl...
>I have a fairly complex spreadsheet that requires salespeople to fill in
>info. Many cells are locked from being changed. Is there a way to create
>a macro that looks a current sheet and will change the cell color if cell
>is locked (or unlocked)? Right now I review by going from cell, to, cell,
>to cell.....
>
> Any suggestions is MUCH appreciated....
> Steve
>
>
>