Search code examples
excelexcel-formulaexcel-2013excel-2016

Conditional Formatting and FormulaText

Trying to solve this question: copy conditional formatting between hyperlinked cells.

I stumbled upon a problem -
Whenever my conditional formatting rule included FORMULATEXT(), it would only evaluate cells that were formulas, no longer looking at basic input cells (like a string or number).

To reproduce:

     A           B           C           D           E
1    =0          =A1         -           -           -
2    ="asdf"     asdf        -           -           -
3    -           -           -           -           -
4    -           -           -           -           -
5    -           -           -           -           -

Conditional Formatting rule (applied to =$A$1:$E$10):

=OR((A1="asdf"),(FORMULATEXT(A1)="=A1"))

Result: enter image description here

For some reason cell B2 is not highlighted even though it should.
Am I missing something? Is this a bug?

I admit this is a quite rare application of these formulas, but I'm curious..

Solution

  • The FORMULATEXT returns an error value in some cases:

    ...

    In the following cases, FORMULATEXT returns the #N/A error value::

    • The cell used as the Reference argument does not contain a formula.

    If a formula parameter is an error value, then the whole formula will return this error value. So the OR will return #N/A instead of True or False. Unless you catch this error:

    =OR(A1="asdf",IFERROR(FORMULATEXT(A1),"")="=A1")