Search code examples
exceldebuggingconditional-formattingdouble-quotesdecimal-point

How to deal with double quotes in conditional formatting's formula


I was having a look at this question, and in order to help, I wrote a conditional formatting rule, based on this formula:

="ABS(B3-A3)>=1,5" // the double-quotes are automatically added by Excel

This, however, seems not to work, although the formula seems to be correct (in the cell at the right, I asked whether this was true and I got a positive result, as you can see (WAAR is Dutch for true)):

Excel screenshot

I'm not interested in the final solution, I just want to know how I can debug such a situation: apparantly the formula is true, but still the conditional formatting is not applied. This might mean that:

  • Something is wrong with the language settings.
  • Something is wrong with the dot/comma decimal separator.
  • Something else is wrong with the formula (absolute references, relative references, ...)

How can I, step by step, evaluate the formula, entered in a conditional formatting configuration, in order to see what might be wrong?

Thanks in advance


Solution

  • Sorry, guys, but the truth is really nerve-wrecking:

    • The answer of Error 1004 is correct. Thanks for the screenshot.
    • The answer of Jario is correct too.

    So what's the deal?

    Apparently there seems to be a problem with the decimal separator : when I enter a dot, using Excel regularly, it gets converted into a comma. When I enter a dot in a conditional formatting formula, this conversion is not done, resulting in a wrong formula, where Excel automatically adds double quotes. As the double quotes result in a wrong formula, the conditional formatting won't be applied.

    So:
    From the moment you see double quotes in a conditional formula, this is an indication that something's wrong, and that your conditional formatting won't work!