Use Excel conditional formatting based on display value and not cell value

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.

Example:

 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?

Answer

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.