How can I quickly identify protected cells?

[Quin]

On my excel worksheet I have a very large mix of cells, some of them are
protected and others are not. I need a way to quickly identify the cells
that are protected so that I can verify that the ones that need protection
are actually protected and the other cells are not protected.

Is there an easy way to quickly determine which cells are protected?
Something like all cells with protection show up as a different color or
something?

Quin

 

[Trevor Shuttleworth]

Quin

You can use Conditional Formatting. For example:

Formula is: =CELL("Protect",D11) and pick a format, for example yellow
background.

Or:

Formula is: =NOT(CELL("Protect",D11)) for unprotected cells.

Regards

Trevor

 

[Gord Dibben]

Sub LockedCells()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
If cl.Locked = True Then
cl.Interior.ColorIndex = 3
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Quin]

Trevor,

Your conditional formatting suggestion works great! I have never used
conditional formatting before so I had a bit of a learning curve but I now
have it working.

Here is what I did…

I selected the entire sheet and then went to Format/Conditional Formatting
and dropped down the box for condition one. I selected “Formula is†and then
I pasted your formula in the next box. Your formula was:
=CELL("Protect",D11) but I changed the cell reference to A1. This insures
that the formula is applied to the correct cells. (I found that if you use
the “Tool†to select cells it gives an absolute reference as indicated by
dollar signs and that will prevent this from working properly). Then I Set
the formatting changes I wanted and clicked ok.

Each cell on an Excel sheet is protected by default so to test it I selected
the entire sheet and went to format/cells/protection and unlocked the cells.
Then I selected just a few test cells and re-locked them. Those cells were
then marked with the special formatting. It is now easy to see exactly which
cells are protected and which are not.

Gord Dibben had a solution that involved VBA code. I tried to paste his
code into a module but it just sat there and did not change my formatting.
Perhaps it needs a trigger or something. I do not know enough about VBA to
say. I will come back to try it again at a later date. It will be worth
learning because with some minor tweaks I bet I could build a convenient
button to make it run.

Thank you for your help,

Quin

 

[Trevor Shuttleworth]

You're welcome. Thanks for the feedback.

To run the macro, use Tools | Macro | Macros... | Run: macro name

Note that this would "permanently" change the background colour. If you
changed any cells, you'd need to manually reset the background colours and
run the macro again.

Regards

Trevor

 

[Bernard]

Hello Quin,

I tried your formula, but I don't understand why we should put A1 or even D11.
I tried with =CELL("PROTECT") and it only highlighted the cells = 1.

Please reply,

Thanks,

Bernard

 

[Dave Peterson]

Select the range that you want to apply that conditional formatting.

Notice the activecell in that selection.

The address of that active cell is the address that you want to use.