Locked cells  
Author Message
Plouplou





PostPosted: Wed Dec 10 04:35:44 CST 2003 Top

Excel Misc >> Locked cells

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.

Excel341  
 
 
J





PostPosted: Wed Dec 10 04:35:44 CST 2003 Top

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




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





PostPosted: Wed Dec 10 20:05:13 CST 2003 Top

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.

--

Dave Peterson