Hi everyone,
Whenever I protect the sheet that has the following code in it and when I try to access the cell it gives me:
Run-time error '1004':
Unable to set the ColorIndex property of the Interior class
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C3:C502")) Is Nothing Then Exit Sub
Dim bcolor As Integer, fcolor As Integer
Select Case Target
Case "Black Arrows on Green"
bcolor = 50: fcolor = 1
Case "White Arrows on Green"
bcolor = 50: fcolor = 2
Case "White Arrows on Red"
bcolor = 3: fcolor = 2
Case "Black Arrows on Yellow"
bcolor = 6: fcolor = 1
Case "White Arrows on Blue"
bcolor = 32: fcolor = 2
End Select
With Target
.Interior.ColorIndex = bcolor
.Font.ColorIndex = fcolor
.Font.Bold = True
End With
End Sub
When I unlock the sheet it works fine.
I select "Select unlocked cells" for allowing the users to use. The drop-down menu on the cell that the code uses is UNLOCKED (in the protection selction of format cells.
Why does this happen and can it be corrected?
Thank you,
Craig
Whenever I protect the sheet that has the following code in it and when I try to access the cell it gives me:
Run-time error '1004':
Unable to set the ColorIndex property of the Interior class
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C3:C502")) Is Nothing Then Exit Sub
Dim bcolor As Integer, fcolor As Integer
Select Case Target
Case "Black Arrows on Green"
bcolor = 50: fcolor = 1
Case "White Arrows on Green"
bcolor = 50: fcolor = 2
Case "White Arrows on Red"
bcolor = 3: fcolor = 2
Case "Black Arrows on Yellow"
bcolor = 6: fcolor = 1
Case "White Arrows on Blue"
bcolor = 32: fcolor = 2
End Select
With Target
.Interior.ColorIndex = bcolor
.Font.ColorIndex = fcolor
.Font.Bold = True
End With
End Sub
When I unlock the sheet it works fine.
I select "Select unlocked cells" for allowing the users to use. The drop-down menu on the cell that the code uses is UNLOCKED (in the protection selction of format cells.
Why does this happen and can it be corrected?
Thank you,
Craig