highlight locked cells
 

I just inherited a very large legacy spreadsheet that has a good bit of
locked cells throughout. Is there a way to maybe iterate through a page and
highlight or other formating of just cells that are locked?

It's very time-consuming to manually check a lot of cells to see if they
have been locked.

highlight locked cells
 

here's a simple way, you can change bold to a color if you want

Sub locked_cells()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Locked = True Then
cell.Font.Bold = True
Else
cell.Font.Bold = False
End If
Next

End Sub

--


Gary


"scott" wrote in message
...
I just inherited a very large legacy spreadsheet that has a good bit of locked
cells throughout. Is there a way to maybe iterate through a page and highlight
or other formating of just cells that are locked?

It's very time-consuming to manually check a lot of cells to see if they have
been locked.

highlight locked cells
 

Are you using Format|Conditional Formatting for anything?

If no, then how about using it to show the locked/unlocked cells?

Select your range (ctrl-A for all the cells).
Format|Conditional formatting
formula is: =CELL("protect",A1)

(Use the activecell's address instead of A1.)

Give it a nice pattern.

scott wrote:

I just inherited a very large legacy spreadsheet that has a good bit of
locked cells throughout. Is there a way to maybe iterate through a page and
highlight or other formating of just cells that are locked?

It's very time-consuming to manually check a lot of cells to see if they
have been locked.

--

Dave Peterson

highlight locked cells
 

By default all cells on the sheet are locked, locked status is only relevant
if the sheet is protected. If the sheet is protected you may (depending on
protect options) need to unprotect before trying Gary's or Dave's
suggestions. If that's not possible you could loop through cells checking if
Not .Locked and apply some format to the unlocked cells.

Regards,
Peter T

"scott" wrote in message
...
I just inherited a very large legacy spreadsheet that has a good bit of
locked cells throughout. Is there a way to maybe iterate through a page
and
highlight or other formating of just cells that are locked?

It's very time-consuming to manually check a lot of cells to see if they
have been locked.

highlight locked cells
 

That's smart. I was looking for a non-distructive method like that.


"Dave Peterson" wrote in message
...
Are you using Format|Conditional Formatting for anything?

If no, then how about using it to show the locked/unlocked cells?

Select your range (ctrl-A for all the cells).
Format|Conditional formatting
formula is: =CELL("protect",A1)

(Use the activecell's address instead of A1.)

Give it a nice pattern.

scott wrote:

I just inherited a very large legacy spreadsheet that has a good bit of
locked cells throughout. Is there a way to maybe iterate through a page
and
highlight or other formating of just cells that are locked?

It's very time-consuming to manually check a lot of cells to see if they
have been locked.

--

Dave Peterson