Solved

# Macro to show which cells are formatted "locked"

Posted on 2011-03-08
Medium Priority
456 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
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

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
``````
0

LVL 20

Expert Comment

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
``````
0

LVL 6

Expert Comment

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

Author Comment

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

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

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

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …
###### Suggested Courses
Course of the Month15 days, 5 hours left to enroll

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

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