PDA

View Full Version : why i am having run time error with this code



gracepinas
01-11-2009, 07:02 PM
i am looking for a code that will help me determine which of the cells have colored then lock the cells

i found one in this forum

but why i am having an error on the line below colored in red


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. One method to do this would be to test the function found 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


If cl.MergeCells = True Then
cl.MergeArea.Locked = True
Else
cl.Locked = True
End If


'
End If

Next cl
Next ws

End Sub

GTO
01-11-2009, 07:37 PM
Greetings gracepinas,

I ran the code w/out any problems. Maybe you might want to attach your workbook with your current code as is, so we could see the "bigger picture" as to what is happening (or not happening as the case may be).

Of course, strip any personal/company/private info first :-)

Hope to help,

Mark

gracepinas
01-11-2009, 10:51 PM
i deleted the part For Each ws In ActiveWorkbook.Worksheets

now its doing ok thanks for the reply

GTO
01-12-2009, 12:13 AM
Hi again gracepinas,

Glad its now working :-) You can mark the thread as Solved by clicking the Thread Tools button right above your first post.

Have a great day!

Mark