Protect Cells worksheet except Yellow Cells - VBA

[raj08536]

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

 

[GWteB]

You are on the right track, one essential line is missing ...

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).EnableSelection = xlUnlockedCells
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

 

[raj08536]

You are on the right track, one essential line is missing ...

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).EnableSelection = xlUnlockedCells
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

Thanks

I tried again but I realized it does'nt go beyond Range.Locked = True

 

[GWteB]

I see, that might happen since Range is a VBA keyword and for that not to be used as a variable name. Replace it with a proper name, such as

Dim MyRange as Range

 

[raj08536]

I see, that might happen since Range is a VBA keyword and for that not to be used as a variable name. Replace it with a proper name, such as

Dim MyRange as Range

Following is my revised code

Sub Lock_Color()
Dim colorIndex As Integer
Dim i As Integer
Dim MyRange As Range
'Lock all the cells that are selected color

colorIndex = 6 '6 = yellow

For i = 1 To ActiveWorkbook.Worksheets.Count
For Each MyRange In Sheets(i).UsedRange.Cells

Dim color As Long
color = MyRange.Interior.colorIndex
If (color = colorIndex) Then
MyRange.Locked = False
Else
MyRange.Locked = True
End If
Next MyRange
'Protect worksheet
Sheets(i).EnableSelection = xlUnlockedCells
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


but i am still getting the error at MyRange.Locked = True

Please help

 

[GWteB]

First of all you have to be sure the worksheet involved is NOT protected. You could include such a line at the beginning of the code loop in question:

Sheets(i).Unprotect Password:="123456"

 

[raj08536]

First of all you have to be sure the worksheet involved is NOT protected. You could include such a line at the beginning of the code loop in question:

Sheets(i).Unprotect Password:="123456"

Looks like I can't lock merged cells. I need merged cells so I can run this code. Can you suggest any thing else?

 

[GWteB]

Looks like I can't lock merged cells. I need merged cells so I can run this code. Can you suggest any thing else?

Regarding merged cells I would say: get rid of them, there's no need for them and they give you issues, one way or the other.
When it comes to formatting your worksheet, you can spread text across multiple cells: Format Cells > Alignment tab > Center Across Selection ...

 

[raj08536]

Regarding merged cells I would say: get rid of them, there's no need for them and they give you issues, one way or the other.
When it comes to formatting your worksheet, you can spread text across multiple cells: Format Cells > Alignment tab > Center Across Selection ...

View attachment 27727
View attachment 27726

Great info......