| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error 1004: Unable to get the axis property | Charts and Charting in Excel | |||
| Hiding Rows Error - "Runtime Error 1004 - Unable to set the hidden property of the range class" | Excel Programming | |||
| run-time error '1004': unable to set the ColorIndex property of the Interior class | Excel Programming | |||
| Interop - "Unable to set the Color property of the Interior class" | Excel Programming | |||
| Unable to set the colorIndex Property of the Interior Class Error | Excel Programming | |||
Linear Mode
