I am working on creating a template for our Sales people. They are only allowed to enter in yellow highlighted cells. I want to protect all worksheets.
Following the code giving me error 1004 at Range.locked = True
Please help
Sub Lock_Color()
Dim colorIndex As Integer
Dim i As Integer
Dim Range As Range
'Lock all the cells that are selected color
colorIndex = 6 '6 = yellow
For i = 1 To ActiveWorkbook.Worksheets.Count
For Each Range In Sheets(i).UsedRange.Cells
Dim color As Long
color = Range.Interior.colorIndex
If (color = colorIndex) Then
Range.Locked = False
Else
Range.Locked = True
End If
Next Range
'Protect worksheet
Sheets(i).Protect Password:="123456", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
MsgBox "Highlighted cells are locked!"
Next i
End Sub
Following the code giving me error 1004 at Range.locked = True
Please help
Sub Lock_Color()
Dim colorIndex As Integer
Dim i As Integer
Dim Range As Range
'Lock all the cells that are selected color
colorIndex = 6 '6 = yellow
For i = 1 To ActiveWorkbook.Worksheets.Count
For Each Range In Sheets(i).UsedRange.Cells
Dim color As Long
color = Range.Interior.colorIndex
If (color = colorIndex) Then
Range.Locked = False
Else
Range.Locked = True
End If
Next Range
'Protect worksheet
Sheets(i).Protect Password:="123456", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
MsgBox "Highlighted cells are locked!"
Next i
End Sub
Last edited: