Selecting Only Unprotected Cells

  • Thread starter Thread starter Chris Guimbellot
  • Start date Start date
C

Chris Guimbellot

Hello,

Excel 2000. I have a spreadsheet that I need to find all unprotected cells
in and change some formatting options for those cells only. The sheet was
designed by someone else and I think that if I try to find them with
trial-and-error, it will either take me forever, or I will never find them
all. I cant find any info so I am hoping someone here may know. Any ideas?
Thanks,

Chris
 
Chris, you could use a macro to change the background color in them so you
can find them easily, like this

Sub Color_Unprotected_cells()
'will change the interior color to blue in all unprotected cells
Cells.Select
For Each Item In Intersect(ActiveSheet.UsedRange, Selection.Cells)
If Item.Locked = False Then
Item.Interior.ColorIndex = 37
End If
Next
Range("A1").Select
End Sub

Or you can put the changes you want in the macro, something like this

Sub Change_Unprotected_cells()
Cells.Select
For Each Item In Intersect(ActiveSheet.UsedRange, Selection.Cells)
If Item.Locked = False Then
Item.Font.Bold = True
Item.Font.Italic = True
Item.Font.Size = 14

End If
Next
Range("A1").Select
End Sub
 
Paul,

Thanks for the quick reply. Two questions though:

1. Is there any way to replicate the desired effect across all sheets in a
workbook? I coped the code to the "ThisWorkbook" tab in the VB code editor,
but it would only work on the first sheet. Also, when I tried to copy the
code to each of the sheets in the code editor, I received error 1004. Any
ideas?

2. If I change a cell from locked to unlocked, do I have to re-run the macro
to see the change in the cell?

Thanks,

Chris
 
Chris,
To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window click on your workbook name, go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your workbook
and press alt and F8, this will bring up a box to pick the Macro from, click
on the Macro name to run it. If you are using excel 2000 or newer you may
have to change the macro security settings to get the macro to run. To
change the security settings go to tools, macro, security, security level
and set it to medium



This macro will REMOVE ALL CELL FILL color and color all unprotected cells
in all sheets blue.



IF YOU HAVE ANY CELLS WITH A BACKGROUND COLOR YOU WANT TO KEEP

let me know and I can change the code.



"If I change a cell from locked to unlocked do I have to re-run the macro
to see the change in the cell?"


Yes, run the code again and it will remove all the fill colors and then
color the unlocked cells blue again



Sub Color_Unprotected_Cells_All_Sheets()
Application.ScreenUpdating = False
Set A = ActiveSheet
For Each Sheet In ThisWorkbook.Worksheets
Sheet.Activate
Cells.Interior.ColorIndex = xlNone
For Each Item In Intersect(ActiveSheet.UsedRange, Cells)
If Item.Locked = False Then
Item.Interior.ColorIndex = 37
End If
Next
Next Sheet
A.Activate
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2003
 
Paul,

Thanks again. I followed the instruction you gave me, and when I ran the
macro, received the following message:

Run-time error '1004':
Unable to set the ColorIndex property of the Interior class.

When I try to debug, when the code view is opened, the following line is
highlighted in yellow:

Cells.Interior.ColorIndex = xlNone

I have no idea what could be the problem. Any ideas? Thanks. Also, thanks
for the answers to the other questions.

Chris
 
Any chance your worksheet is protected?

If yes, then unprotect it before running Paul's code.
 
Dave,

You were right. That was the problem. It works great now. Thanks to both of
you.

Chris
 
Back
Top