cdbyler
09-05-2007, 11:44 AM
Hi,
I just found the UnprotectGreenCells routine by Ken Puls. It does exectly what I want it to do except it does not work when I insert the code into an existing workbook with multiple sheets. Works fine in Ken's test sheets, works fine when I create new sheets, but not in my existing workbook. I get a "run-time error 1004: Unable to set the locked property of the range class". I have unprotected the sheets and unlocked the cells. This has to be something simple but it's by me. Code below:
Option Explicit
Sub UnprotectGreenCells()
'Macro purpose: To unlock all green cells
'NOTE: All worksheets in the workbook must be
' unprotected before this procedure is run
Dim cl As Range, ws As Worksheet, lColor As Long
'Set the cell color that you want to protect
lColor = 35 'Green
'It may be a good idea to test all sheets to see if any are protected
'here.
'Cycle through each worksheet in the workbook
For Each ws In ActiveWorkbook.Worksheets
For Each cl In ws.UsedRange
'Change colored cell to unlocked, and
'all other cells to locked
If cl.Interior.ColorIndex = lColor Then
cl.Locked = False
Else
cl.Locked = True ' Blows up on this line :banghead:
End If
Next cl
Next ws
End Sub
Any help is appreciated,
Chris
I just found the UnprotectGreenCells routine by Ken Puls. It does exectly what I want it to do except it does not work when I insert the code into an existing workbook with multiple sheets. Works fine in Ken's test sheets, works fine when I create new sheets, but not in my existing workbook. I get a "run-time error 1004: Unable to set the locked property of the range class". I have unprotected the sheets and unlocked the cells. This has to be something simple but it's by me. Code below:
Option Explicit
Sub UnprotectGreenCells()
'Macro purpose: To unlock all green cells
'NOTE: All worksheets in the workbook must be
' unprotected before this procedure is run
Dim cl As Range, ws As Worksheet, lColor As Long
'Set the cell color that you want to protect
lColor = 35 'Green
'It may be a good idea to test all sheets to see if any are protected
'here.
'Cycle through each worksheet in the workbook
For Each ws In ActiveWorkbook.Worksheets
For Each cl In ws.UsedRange
'Change colored cell to unlocked, and
'all other cells to locked
If cl.Interior.ColorIndex = lColor Then
cl.Locked = False
Else
cl.Locked = True ' Blows up on this line :banghead:
End If
Next cl
Next ws
End Sub
Any help is appreciated,
Chris