CODEWITHSUNDEEP
excelexcel-formularuntime-errorconditional-formattingvba
Eran Elad
Eran Elad

Reputation: 11

Excel VBA conditional formatting - correct syntax for formula1 argument

There is a problem with conditional formatting via Excel VBA run on Excel 2016. I have the following simple code:

With targetSheet.Range("J:J")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:="=AND(ISBLANK($J1), NOT(ISBLANK($A1)), ISBLANK($K1))"
        .FormatConditions(1).Interior.ColorIndex = 53
End With

(targetSheet is defined elsewhere so no need to worry about it).

This code works great on my computer. There is no run-time error and when I open the conditional formatting popup I see the correct formula as follow:

"=AND(ISBLANK($J1), NOT(ISBLANK($A1)), ISBLANK($K1))"

(including the quotation marks)

When I send this Excel to other people in the office (All of us have Excel 2016), some of them receive the following error:

"Run-time error '5': Invalid procedure call or argument."

After debugging, I found that the Error is because of the =AND operator.

If I remove the = (equal sign) it doesn't fail at run-time, but the conditional formatting functionality is wrong. When I open the conditional formatting popup, it shows:

="AND(ISBLANK($J1), NOT(ISBLANK($A1)), ISBLANK($K1))"

What is the correct syntax for Formula1:?

Upvotes: 1

Views: 1118

Answers (1)

Rory
Rory

Reputation: 34045

Unfortunately, the CF formula has to be coded exactly as it would be entered on the target machine, so is susceptible to language and regional issues between computers. What you can do is have the code enter the formula into a cell, then read back the FormulaLocal property of that cell, and use that in the CF settings.

Upvotes: 2

Related Questions