"Error 1004 Unable to set the ColorIndex property of the Interior.

[Bob Barnes]

....class"...

I am an Access Programmer who was asked to do conditional formatting for
more than 3 colors. I got code from Google that I modified to the code
below...it has worked well for maybe 2 years until Friday...See..."' THIS is
where it FAILS...
below..H E L P...TIA - Bob

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, grades As Range, cell As Range
On Error GoTo AAA1
'Set grades = Range("A:A") 'change column ref as required
Set grades = Range("Bill1"): bBill1 = True: GoTo BBB
AOne:
Set grades = Range("Bill2"): bBill2 = True: GoTo BBB
ATwo:
Set grades = Range("Bill3"): bBill3 = True: GoTo BBB
AThree:
Set grades = Range("Bill4"): bBill4 = True: GoTo BBB
AFour:
Set grades = Range("Bill5"): bBill5 = True: GoTo BBB
AFive:
Set grades = Range("Bill6"): bBill6 = True: GoTo BBB
ASix:
Set grades = Range("Bill7"): bBill7 = True: GoTo BBB
ASeven:
Set grades = Range("Bill8"): bBill8 = True: GoTo BBB
AEight:
Set grades = Range("Bill9")
BBB:
Set rng = Intersect(grades, ActiveSheet.UsedRange)
For Each cell In rng
'If cell.Value < 3 And cell.Value <> "" Then
'Else: cell.Interior.ColorIndex = xlNone
'Colors... 0 (white), 3 (red), 4 (green), 5 (blue)
'Colors... 6 (yellow)
If ((cell.Value <= 0.79 And cell.Value <> "") _
And (cell.Value >= 0 And cell.Value <> "")) Then
cell.Interior.ColorIndex = 3
ElseIf ((cell.Value > 0.79 And cell.Value <> "") _
And (cell.Value < 0.9 And cell.Value <> "")) Then
cell.Interior.ColorIndex = 6
' THIS is where it FAILS...
ElseIf cell.Value >= 0.9 And cell.Value <> "" Then
cell.Interior.ColorIndex = 4
ElseIf cell.Value = "" Then
cell.Interior.ColorIndex = 0
End If
Next
If bBill1 = True Then bBill1 = False: GoTo AOne
If bBill2 = True Then bBill2 = False: GoTo ATwo
If bBill3 = True Then bBill3 = False: GoTo AThree
If bBill4 = True Then bBill4 = False: GoTo AFour
If bBill5 = True Then bBill5 = False: GoTo AFive
If bBill6 = True Then bBill6 = False: GoTo ASix
If bBill7 = True Then bBill7 = False: GoTo ASeven
If bBill8 = True Then bBill8 = False: GoTo AEight
AAA2:
Exit Sub
AAA1:
Stop
Select Case Err
Case 13
Resume
Case 2185
Resume Next
Case Else
MsgBox "Error " & Err.Number & " " & Err.Description: Resume Next
End Select
End Sub

 

[Peter T]

' THIS is where it FAILS...

ElseIf cell.Value >= 0.9 And cell.Value <> "" Then
cell.Interior.ColorIndex = 4

Is this particular cell locked and the sheet protected, perhaps.

FWIW, the additional condition check - And cell.Value <> "" - would appear
redundant.

Regards,
Peter T

 

[Bob Barnes]

Peter T -

I agree on the redundant code.

Will have the User check the cell locked and protected sheet.

Bob

 

[Bob Barnes]

Is this particular cell locked and the sheet protected, perhaps...
That was it.

Thanks again - Bob