I have a cell with the following conditions to compare its value against the cell in the previous row.

Red Highlight: Cell Value <> =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0)

Green Highlight: Cell Value = =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0)

This works well, except for if I use a formula in the cell. When I do, the first highlight (red) is always chosen. I'm guessing because the "value" of the cell is the formula, and not the result of the formula.

 A1 := =24
 A2 := =DEC2HEX(36) 

A2 would be formatted "red", despite both cells displaying the text "24".

Is it possible for conditional formatting to compare based on the displayed text in the cell, and not the underlying formulas?

  • 1
    you could use =LEFT(A1,LEN(A1))=A2 Sep 27 '16 at 20:40

First, if you want to check the cell above in a conditional formatting formula, you don't need that complicated Offset/Indirect construct. Just select A2 and in your conditional format formula reference A1 as a relative reference, i.e. without any $ signs. Then apply that conditional format to other cells and it will always evaluate the cell one row above the current cell.

Getting back to your question: no, the format of a cell is not important. Conditional formatting will evaluate the underlying value.

In the case of =DEC2HEX(36), though, the output is not a number, the comparison will return FALSE if this cell is compared with the numeric value in A1.

  • Is there a way to change the comparison so that DEC2HEX(36) = 24 evaluates to be TRUE?
    – lcs
    Sep 27 '16 at 20:34
  • You need to coerce the text into a number: =(DEC2HEX(36)+0)=24 returns TRUE, but Hex 24 is not equal to 24, so that will not make sense.
    – teylyn
    Sep 27 '16 at 20:49
  • I ended up just adding a second comparison and letting them fall through. One compared TEXT(Above, "0") and the other compared just Above.
    – lcs
    Sep 28 '16 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.