.interior.color.index = RGB suddenly leading to Run-time error 5 "Invalid procedure, call or argument"

[metero75]

Hi all, the below macro has been working fine for years and is not working anymore since today. I am on a Mac.

It blocks each time it tries going through the "=RGB" functions such as the following, which returns Run-time error 5 "Invalid procedure, call or argument".
ElseIf Target.Interior.Color = RGB(255, 94, 94)

Can anyone please help?

---

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  
    If Target.Column = Range("Inventory_ID").Column Then
        If Target.Interior.Color = 16777215 Then
            Range(Target, Range("Inventory_Note").Offset(Target.Row - Range("Inventory_ID").Row)).Interior.Color = RGB(255, 94, 94)
            Range("Inventory_Available").Offset(Target.Row - Range("Inventory_ID").Row) = "N"
        ElseIf Target.Interior.Color = RGB(255, 94, 94) Then
            Range(Range("Inventory_ID").Offset(Target.Row - Range("Inventory_ID").Row), Range("Inventory_Note").Offset(Target.Row - Range("Inventory_ID").Row)).Interior.Color = RGB(255, 204, 153)
            Range("Inventory_Available").Offset(Target.Row - Range("Inventory_ID").Row) = "N"
        ElseIf Target.Interior.Color = RGB(255, 204, 153) Then
            Range(Range("Inventory_ID").Offset(Target.Row - Range("Inventory_ID").Row), Range("Inventory_Note").Offset(Target.Row - Range("Inventory_ID").Row)).Interior.Color = xlNone
            Range("Inventory_Available").Offset(Target.Row - Range("Inventory_ID").Row) = "Y"
        'ElseIf Target.Interior.Color = RGB(255, 127, 0) Then
        '    Range(Range("Inventory_ID").Offset(Target.Row - Range("Inventory_ID").Row), Range("Inventory_Note").Offset(Target.Row - Range("Inventory_ID").Row)).Interior.Color = RGB(150, 131, 236)
        'ElseIf Target.Interior.Color = RGB(150, 131, 236) Then
        '    Range(Range("Inventory_ID").Offset(Target.Row - Range("Inventory_ID").Row), Range("Inventory_Note").Offset(Target.Row - Range("Inventory_ID").Row)).Interior.Color = xlNone
        End If
    ElseIf (Target.Column = Range("Inventory_Online").Column Or Target.Column = Range("Inventory_Signed").Column Or Target.Column = Range("Inventory_COA").Column Or Target.Column = Range("Inventory_Framed").Column Or Target.Column = Range("Inventory_Buy_Invoice").Column) And Target.Row > Range("Inventory_Online").Row And Target.Row <= Range("Inventory_ID").End(xlDown).Row Then
        If Target.Value = "Y" Then
            Target.Value = "N"
        ElseIf Target.Value = "N" Then
            Target.Value = "Y"
        Else:
            Target.Value = "N"
        End If
    End If
   
    Cancel = True
   
End Sub

---

Thanks.

 

[mikerickson]

It sounds like you might have a reference not open.
What happens if you add

Dim x as OLE_Color

at the beginning of the routine

 

[metero75]

Thanks but it does not change anything - same error.

 

[kanadaaa]

Does anything change if you rewrite this line

If Target.Interior.Color = 16777215 Then

as

If Target.Interior.Color = xlWhite Then

or

If Target.Interior.Color = xlNone Then

 

[metero75]

Thanks but no because that line of code works. It is only the lines with "=RGB(x,x,x)" that do not work.

As I said they have been working for years and I don't know why today they don't work anymore. My file has more rows than before but that is it.

 

[kanadaaa]

Maybe you accidentally changed or deleted range names?
Check them out.

 

[metero75]

No it's not that. I can run the following:
Range(Range("Inventory_ID").Offset(Target.Row - Range("Inventory_ID").Row), Range("Inventory_Note").Offset(Target.Row - Range("Inventory_ID").Row)).Interior.Color.Select

But if I run:
Range(Range("Inventory_ID").Offset(Target.Row - Range("Inventory_ID").Row), Range("Inventory_Note").Offset(Target.Row - Range("Inventory_ID").Row)).Interior.Color=RGB(255,94,94) that does not work.

It really looks like it is the RGB function.

 

[mikerickson]

What happens when you type ?RGB(0,00) in the Immediate Window

 

[Rick Rothstein]

Try this experiment and let us know if it worked or not... replace your RGB function with vba.RGB and tell us what happens.

 

[metero75]

Thanks but still same issue: "Run-time error 5! Invalid procedure, call or argument".