Locked cells

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I have a very large workbook with many locked cells on
each sheet. Is there any way of showing which are the
locked cells, without having to test each individually.
Many thanks for any help.
 
one way:

This will shade all the locked cells in the used range. This does
not check whether worksheet protection is actually applied.

Public Sub ShadeLocked()
Dim rLocked As Range
Dim rCell As Range

For Each rCell In ActiveSheet.UsedRange
If rCell.Locked Then
If rLocked Is Nothing Then
Set rLocked = rCell
Else
Set rLocked = Union(rLocked, rCell)
End If
End If
Next rCell
If Not rLocked Is Nothing Then _
rLocked.Interior.ColorIndex = 12
End Sub


If you're not familiar with macros, see David McRitchie's Getting
Started with macros page:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Another option if you can use Format|conditional formatting.

Select your range and then format|conditional formatting.
(I'm gonna use A1 as my activecell in my selection. Modify it to match your
data.)

Formula is =cell("protect",a1)

and give it a nice pattern shade.
 
Back
Top