1

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

Community
  • 1
  • 1
Eran Elad
  • 11
  • 1
  • What version of Excel are you using? And how did you find that the error is due to the `=And`? – SJR Nov 08 '17 at 11:44

1 Answers1

2

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.

Rory
  • 32,730
  • 5
  • 32
  • 35
  • Thanks. You answer gave me a clue to find the problem. – Eran Elad Nov 08 '17 at 14:14
  • In my Excel, the arguments in functions should be comma separated. In the other computers, it is semi-colon instead. for example, in my Excel I should write "=AND(Arg1, Arg2)". In their Excel you should write: "=AND (arg1;arg2)". I guess it's a locale issue. So, I want to add a comma or a semi-colon based on the locale. do you know how to ask this condition in the code? – Eran Elad Nov 08 '17 at 14:17
  • In addition to @Rory 's valid answer, my simple work around to return the *local CF formula* might be of some interest [Highlight series of dates that met conditions](https://stackoverflow.com/questions/51751330/highlight-series-of-dates-that-met-conditions/51829017#51829017) – T.M. Aug 13 '18 at 19:30
  • @T.M. Isn't that exactly the same as my suggestion? – Rory Aug 14 '18 at 07:06
  • My apologies, of course you are right, apparently I missed to study the second line of your answer; nevertheless my example code could be helpful :-) @Rory – T.M. Aug 14 '18 at 07:28
  • 1
    @T.M. Totally agree! Just checking I hadn't missed something. :) – Rory Aug 14 '18 at 07:40