Excel should show which cells have protection

R

Rich the Pool Guy

I make frequent use of large budget worksheets that require input by many
users, and I use cell protection to ensure that some data entry cells are
available, while others are not, and all formulas are protected to prevent
accidental overwriting or erasure.

If it doesn't already, Excel should have some feature, similar to Formula
Audting mode, that highlights all cells that are currently protected (or
unprotected - either way works). This would greatly ease the difficulty of
making sure all the protections are corrrect.
 
G

Gary Brown

Using Conditional Formatting, you can

1) highlight the entire worksheet
2) select Format / Conditional Formatting...
3) Change 'Cell value is' to 'Formula is'
4) Enter...
=CELL("protect",A1)=0
in the 'conditions' box
Note: 0 = unlocked cells
Note: 1 = locked cells
5) select the formating desired

This will highlight all unlocked cells.

HTH,
Gary Brown
 
R

Ron Rosenfeld

On Mon, 27 Sep 2004 10:11:02 -0700, "Rich the Pool Guy" <Rich the Pool
I make frequent use of large budget worksheets that require input by many
users, and I use cell protection to ensure that some data entry cells are
available, while others are not, and all formulas are protected to prevent
accidental overwriting or erasure.

If it doesn't already, Excel should have some feature, similar to Formula
Audting mode, that highlights all cells that are currently protected (or
unprotected - either way works). This would greatly ease the difficulty of
making sure all the protections are corrrect.
Click to expand...

Simple enough to write a VBA UDF and use it as a Conditional Formatting
Formula.

=================
Function IsLocked(rg As Range) As Boolean
IsLocked = rg.Locked
End Function
==================

Will return TRUE if the cell is locked.

You can then apply that format to your worksheet.

Select the range to which you want this to apply.

Format/Conditional Formatting
Formula Is: =islocked(A1)=TRUE

Replace A1 with the Active Cell and the formatting should copy correctly to all
the selected cells.
 
Top