1
votes

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?

2

2 Answers

1
votes

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

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

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.

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

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.

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.