Solved

Macro to show which cells are formatted "locked"

Posted on 2011-03-08
6
450 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
6 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 350 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:pari123
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 150 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

777 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