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
StuartOrdAsked:
Who is Participating?
 
Saurabh Singh TeotiaConnect With a Mentor Commented:

You can use this code to do what you are looking for...

Saurabh...
Sub checkdata()
    Dim rng As Range, cell As Range

    ActiveSheet.Unprotect Password:="password"  '<----Give your sheet password

    Set rng = Range("A1:N100")           '<----  Define your range you want to check

    For Each cell In rng
        If cell.Locked = True Then
            cell.Interior.ColorIndex = 3         '<----- Cell color if the cell is locked you can change the number as per you..
        Else
            cell.Interior.ColorIndex = 6     '<----- Cell color if the cell is locked you can change the number as per you..
        End If
    Next cell

    ActiveSheet.Protect Password:="password"   '<--- Give your sheet password.



End Sub

Open in new window

0
 
Ardhendu SarangiSr. Project ManagerCommented:
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

0
 
royhsiaoCommented:
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
StuartOrdAuthor Commented:
saurabh726,
That worked fine! Thanks.

pari123
I'm using 2003, so maybe that's why it failed at:
.Pattern = xlSolid
0
 
DaveConnect With a Mentor Commented:
You may find this article useful,  
A fast method for determining the unlocked cell range http://www.experts-exchange.com/A_2769.html

It provides a very fast technique for returning the unlocked cell range without looping

You could use it in conjunction with
Adding a "Subtract Range" method alongside Union & Intersect  Article   http://www.experts-exchange.com/A_2974.html
to return the locked cell range (ie subtract the unlocked range from the usedrange)

Cheers

Dave
0
 
StuartOrdAuthor Commented:
brettdj,
Yes I can see that adding some features of this new article to saurabh726's code makes a good solution. I realised i needed to copy my sheet as many sheets have colour formatting that I didn't want to lose, and this method makes an automatic copy and doesn't recolour the sheets, so i can pick and choose a suitable hybrid.
Thanks all, excellent help as usual
Stuart
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.