Error with Protecting Sheets

[craigwojo]

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

 

[Jaafar Tribak]

See if this works : Edit the code as follows:

Me.Unprotect  '\\ temporarly unprotect sheet
With Target
.Interior.ColorIndex = bcolor
.Font.ColorIndex = fcolor
.Font.Bold = True
End With
Me.Protect '\\ restore protection

Regards.

 

[Bartek]

Hi,

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

If you are using Excel 2002 or latter you may protect the worksheet with the option AllowFormattingCells:=True - this will solve your problem. AllowFormattingCells will not work in Excel 2000 - in Excel 2000 you may change the font.colorindex of unlocked cells, but not interior.colorindex. In that case, you have to temporarily unprotect the worksheets, as described above.

 

[craigwojo]

Jaafar,

Where do I put the code? Do I insert it within the code?

Thank you,
Craig

See if this works : Edit the code as follows:

Me.Unprotect  '\\ temporarly unprotect sheet
With Target
.Interior.ColorIndex = bcolor
.Font.ColorIndex = fcolor
.Font.Bold = True
End With
Me.Protect '\\ restore protection

Regards.