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.
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:
> 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.
Excel Misc >> Locked cells
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.
>
> 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.