zoom38
03-15-2006, 05:42 PM
My worksheet works fine but when I protect the sheet I get the following error message: "Run-Time Error '1004':", "Unable to set the ColorIndex property of the interior class". I am assuming I have the sub in the wrong spot but I really don't know. Anyone know why Im getting this error message and how to solve it?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Sh.Range("B8:O34")) Is Nothing Then
Select Case Target.Value
Case "V.5", "V1" To "V999"
Target.Interior.ColorIndex = 35
Case "PL.5", "PL1" To "PL999"
Target.Interior.ColorIndex = 34
Case "SL.5", "SL1" To "SL999", "FS.5", "FS1" To "FS999"
Target.Interior.ColorIndex = 36
Case "BL.5", "BL1" To "BL999"
Target.Interior.ColorIndex = 36
Case Is = "ML.5", "ML1" To "ML999"
Target.Interior.ColorIndex = 24
Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select
End If
End Sub
When I click on Debug it highlights "Target.Interior.ColorIndex = -4142" on the Case Else line.
Thanks
Gary
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Sh.Range("B8:O34")) Is Nothing Then
Select Case Target.Value
Case "V.5", "V1" To "V999"
Target.Interior.ColorIndex = 35
Case "PL.5", "PL1" To "PL999"
Target.Interior.ColorIndex = 34
Case "SL.5", "SL1" To "SL999", "FS.5", "FS1" To "FS999"
Target.Interior.ColorIndex = 36
Case "BL.5", "BL1" To "BL999"
Target.Interior.ColorIndex = 36
Case Is = "ML.5", "ML1" To "ML999"
Target.Interior.ColorIndex = 24
Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select
End If
End Sub
When I click on Debug it highlights "Target.Interior.ColorIndex = -4142" on the Case Else line.
Thanks
Gary