highlight locked cells

[scott]

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.

 

[Gary Keramidas]

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

 

[Dave Peterson]

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.

 

[Peter T]

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]

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