CODEWITHSUNDEEP
vbaexcelruntime-errorconditional-formatting
durba138
durba138

Reputation: 37

Run time error over conditional formatting in VBA

So this was working, highlighting the cells to the right of the checkaddress value. Now it isn't and I cant figure out why. It gives the error "Run-time error '5': Invalid procedure call or argument". The error is in the first .Add statement, but commented it out, all the .Add statements error. 

Sub Highlight4()

For i = 1 To ActiveSheet.Cells(Rows.Count, 4).End(xlUp).row Step 2

    If Cells(i, 4) = "Metric" Then
        For j = 1 To 15

        Dim r As Range
        Set r = Range(Cells(i, j * 4 + 2), Cells(i + 1, j * 4 + 4))

        Dim checkAddress As String
        checkAddress = Cells(i, j * 4 + 1).Address

        With r.FormatConditions
            .Delete

            .Add Type:=xlExpression, Formula1:="=" & checkAddress & " = 0"
            .Item(.Count).Interior.Color = rgbRed

            .Add Type:=xlExpression, Formula1:="=" & checkAddress & " = 15"
            .Item(.Count).Interior.Color = rgbGold

            .Add Type:=xlExpression, Formula1:="=" & checkAddress & " = 25"
            .Item(.Count).Interior.Color = rgbGreen
        End With

        Next j
    End If
Next i
End Sub

Upvotes: 1

Views: 445

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

There is nothing wrong with your code. You are getting that error because Excel is running in R1C1 mode. Switch it off and it will be fine :)

  1. Click on File | Options | Formulas
  2. Un check R1C1 Reference Style

enter image description here

Upvotes: 1

Related Questions