Displaying locked cells

M

MikeC

I know that you can tab to unlocked cells, but when you
have been designing a spreadsheet, is there a way of
displaying which cells are locked (protected) and which
are not?
 
M

MikeC

-----Original Message-----
Mike

I like to use a conditional format for this:

In cell A1, call up Format / Conditional Format. In the
Click to expand...
drop down box select "Formula is" and then enter the
following.
=CELL("Protect",A1)=1

Select a suitable cell background colour (or font colour,
Click to expand...
or whatever) for the condition then OK. Copy your format
to the rest of your spreadsheet and Bob's yer uncle. Also,
I added the cell lock/unlock command button to my standard
toolbar so I can toggle between locked and unlocked status
for my current selection. Works a treat.
.
Click to expand...
Thanks Vaughan, I was hoping there would be a built-in
function for doing it rather than having to format it
manually like that. It's a bit cumbersome if you have
conditional formatting on the sheet already - it means
another condition for each cell. I guess it works OK as
long as you start the project by putting the condition on
every cell you'll be using.

Hmmm... not a bad idea. Thanks again.

Mike.
 
M

MikeC

-----Original Message-----
Well, the other thing you can do is create a Protected
Click to expand...
style and apply that instead of directly changing the
protection status. Its kind of back-to-front though. I
wouldn't really prefer it.Thanks again,

It has certainly given me something to chew on.

Best regards,

Mike.
 
G

Gord Dibben

Mike

If you can use a macro..........

Sub Locked_Cells()
Dim cell As Range, tempR As Range, rangeToCheck As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell.Locked Then
If tempR Is Nothing Then
Set tempR = cell
Else
Set tempR = Union(tempR, cell)
End If
End If
Next cell
If tempR Is Nothing Then
MsgBox "There are no Locked cells " & _
"in the selected range."
End
End If
tempR.Select 'Interior.ColorIndex = 3
End Sub

Gord Dibben Excel MVP
 
Top