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

....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

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

' 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" wrote in message
...
...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

"Error 1004 Unable to set the ColorIndex property of the Inter
 

Peter T -

I agree on the redundant code.

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

Bob

"Peter T" wrote:

' 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" wrote in message
...
...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

"Error 1004 Unable to set the ColorIndex property of the Inter
 

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

Thanks again - Bob

"Peter T" wrote:

' 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" wrote in message
...
...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