?
Solved

Macro to show which cells are formatted "locked"

Posted on 2011-03-08
6
Medium Priority
?
454 Views
Last Modified: 2012-05-11
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
Comment
Question by:StuartOrd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 1400 total points
ID: 35074532

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
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35074555
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
 
LVL 6

Expert Comment

by:royhsiao
ID: 35074674
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:StuartOrd
ID: 35075270
saurabh726,
That worked fine! Thanks.

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

Assisted Solution

by:Dave Brett
Dave Brett earned 600 total points
ID: 35075828
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
 

Author Comment

by:StuartOrd
ID: 35079550
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

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question