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.

这是有效的，在checkaddress值的右边突出显示单元格。现在不是，我也搞不懂为什么。它给出错误“运行时错误‘5’:无效过程调用或参数”。错误在第一个。add语句中，但是注释掉了它，所有。add statement错误。

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

1 个解决方案

#1

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 :)

你的代码没有问题。您会得到这个错误，因为Excel在R1C1模式下运行。关掉它，一切都会好起来。

1. Click on File | Options | Formulas
2. 点击文件|选项|公式。
3. Un check R1C1 Reference Style
4. uncheck R1C1引用样式