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

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


"Quin" wrote in message
...
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

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

On Sat, 25 Nov 2006 14:27:01 -0800, Quin wrote:

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,

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" wrote:

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


"Quin" wrote in message
...
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

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


"Quin" wrote in message
...
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" wrote:

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


"Quin" wrote in message
...
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

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

"Quin" wrote:

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" wrote:

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


"Quin" wrote in message
...
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

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.

Bernard wrote:

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

"Quin" wrote:

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" wrote:

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


"Quin" wrote in message
...
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




--

Dave Peterson