asked on

Macro to show which cells are formatted "locked"

I'd like to see which cells are locked and which are unlocked on a sheet containing a mixture of locked and unlocked cells.

Can anyone help with a macros that shows all cells that are locked or unlocked, eg by shading the cells different colours?

Thanks

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Ardhendu Sarangi

Try this -

Please let me know if this works.

this is for office 2007.

- ardhendu

Option Explicit
Sub Macro1()
    Dim cel As Range
    For Each cel In Selection
        If cel.Locked = False Then
            With cel.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorLight2
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
            End With
        Else
            With cel.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
            End With
        End If
    Next
End Sub

Open in new window

Member_2_6169280

Nice job Pari123.
If you add cells.select below Dim Cel as range in Pari 123's code like the following:
====
Dim cel As Range
Cells.Select
====
It will check all cells. When you find the cells just click Esc to stop the macros.

StuartOrd

ASKER

saurabh726,
That worked fine! Thanks.

pari123
I'm using 2003, so maybe that's why it failed at:
.Pattern = xlSolid

SOLUTION

Dave