运行时错误13类型不匹配条件格式

I have code:

我有代码:

Dim Formul1 As String
Dim Formul2 As String


Formul2 = "=AND(R[1]C<=R[1]C[-1];(R[1]C+7)>R[1]C[-1])"
**Formul1 = Application.ConvertFormula(Formula:=Formul2, fromreferencestyle:=xlR1C1, toreferencestyle:=xlA1)**

With Range("$H$6:$FH$50")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:=Formul1
    .FormatConditions(1).Interior.ColorIndex = 3
End With

and it gives me an error in highlighted place Run time error 13 type mismatch conditional formatting, I dont know why, any ideas?

它给了我一个错误在突出显示的地方运行时错误13类型不匹配条件格式，我不知道为什么，有什么想法吗?

2 个解决方案

#1

Issue in Formul2, Change the AND Condition.. Use Comma instead of Semicolon.. should go fine.

在公式2中发布，改变和条件。用逗号代替分号。应该很好。

Formul2 = "=AND(R[1]C<=R[1]C[-1],(R[1]C+7)>R[1]C[-1])"

#2

In addition to your Formul2 using the semi-colon as the system list delimiter when your actual system is using the comma, you are also going to have to implement the RelativeTo parameter of the ConvertFormula method. An xlR1C1 style formula with relative cell references looks exactly the same no matter where it is on a worksheet. However, an xlA1 style changes depending on where it is.

除了使用分号作为系统列表分隔符的公式2之外，在实际系统使用逗号时，还需要实现ConvertFormula方法的相对参数。带有相对单元格引用的xlR1C1样式公式无论在工作表的哪个位置看起来都是一样的。但是，xlA1样式会根据它的位置而变化。

The nature of what you are attempting to accomplish is not entirely clear but I would make an intelligent guess that you need to use RelativeTo:=Range("H6") as H6 is the cell in the top left corner of your CF rule's Applies to:.

您试图实现的目标的性质并不完全清楚，但我可以明智地猜测您需要使用相对论性:=Range(“H6”)，因为H6是您的CF规则左上角的单元格:。

Formul1 = Application.ConvertFormula(Formula:=Formul2, fromreferencestyle:=xlR1C1, toreferencestyle:=xlA1, RelativeTo:=Range("H6"))

It also seems likely that you may want to lock the column references for that wide Applies to: so that only columns G and H are considered but you will have to verify your intentions in that regard.

您可能还希望锁定列引用，因为它适用范围很广:因此只考虑列G和H，但您必须验证您的意图。

FWIW, you do not have to change an xlR1C1 style formula to xlA1 style before using it to create or modify a FormatConditions Collection Object. Either style is accepted.

在使用xlR1C1样式公式创建或修改FormatConditions集合对象之前，不需要将它更改为xlA1样式。风格是接受。

#1