• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

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
0
StuartOrd
Asked:
StuartOrd
2 Solutions
 
Saurabh Singh TeotiaCommented:

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
StuartOrdAuthor Commented:
saurabh726,
That worked fine! Thanks.

pari123
I'm using 2003, so maybe that's why it failed at:
.Pattern = xlSolid
0
 
Dave BrettCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now