Can I determine which cells on a sheet are locked and which are not?

[Sherry]

Is there any kind of report, etc. I could look at that
would tell me what cels are unlocked or vice versa on a
protected worksheet?

I have 8 worksheetsin a workbook, with each sheet
comprised of more than 550 rows and 14 columns. I could
tab from cell to cell to determine if I have all the
desired cells locked, but with nearly 60,000 cells, that
is not at all feasible.

Does anybody have any ideas? Basically all I'm wanting to
do is to be sure that all of my cells that need to be are
locked, so that when I share this workbook I need not
worry about others getting into areas they should not have
the ability to modify.

Thanks!

 

[Ken Wright]

Quick and dirty, and YOU MUST SAVE YOUR WORKBOOK FIRST!!!, or better still work
on a copy of it, because you can't generally undo the results of a macro.

This routine will work through each sheet and flag every unlocked cell in the
usedrange as bright red. As long as your workbook is aved first though you can
just close and not save, but I'd still recommend working on a copy.

Sub ShowUnlockedCells()

Dim Sht As Worksheet
Dim cell As Range

For Each Sht In ActiveWorkbook.Worksheets
For Each cell In Sht.UsedRange
If cell.Locked = False Then
cell.Interior.ColorIndex = 3
End If
Next cell
Next Sht
End Sub

 

[Dave Peterson]

If you're not using Format|conditional formatting, you could use that:

Select your range, the Format|conditional formatting, then formula is:
=CELL("protect",A1)
(I had A1 as the active cell)

then format like you want (light pastel colors are my favorite).

Remove the conditional formatting later (if you want).