Conditional Formatting bason on cell locked/unlocked status

[mrcshaky]

Regarding the protection status of a cell, I would like to know if there is a way to visually identify the cells that are 'unlocked' compared to those that are unlocked.

Can this be done with conditional formatting or other means?

The best I can do now is hilight the cell and see the locked/unlocked icon in the protection toolbar change back and forth ... i would like to be able to view an area of my worksheet and see if i have forgotten to lock down or unlock cells when doing data pulls/queries.

Thanks for any assistance.

Mike

 

[Banker1]

You could try something like this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> UnLocked()
<SPAN style="color:#00007F">Dim</SPAN> WorkRange <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("A1", ActiveCell.SpecialCells(xlCellTypeLastCell))

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> WorkRange
    <SPAN style="color:#00007F">If</SPAN> Cell.Locked = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Cell.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        Cell.Interior.ColorIndex = xlNone
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> Cell

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It will highlight unlocked cells in yellow. You can adjust the code to flag the cells differently if you would like.

EDIT: The only issue I can think of with the code above is... if you have existing color formats on a locked cell it will be cleared (no fill) based on it's locked status. I'm struggling with the code to keep the existing color format if the cell is locked rather than clearing the format.

 

[barry houdini]

It's possible to use conditional formatting if you wish...

Format > Conditional Formatting

use "formula is"

=CELL("protect",A1)

format as required

This will highlight all cells which are locked (whether or not Protection is applied)