Search code examples
excelformattingconditional-statementsformula

how do I compare each row of two different columns and highlight the highest value (not formula) in each row?

I have two columns to compare. All cell values come from the ROUNDUP function. =ROUNDUP(C6/D12,0) etc.

enter image description here

I want the larger, or equal, of the two in each row to be green and the smaller red. Using the formula, it does not work as expected. If I do the same with numbers typed, not the formula, it works. It appears the formatting applies to the formula and not the value.

That is the first half of the problem. I also want to autofill/paint the conditional formatting to numerous cells, but it always compares to the top left cell, rather than the two cells on the same row.

If I use the color scales formatting it works, but I do not want the scales, just red/green.

It seems hard to believe that what I want to do is not possible. Can someone please help me with this. Thanks in advance.

Solution

  • In conditional formatting, under 'use a formula to determine which cells to format', you need to enter

    =A2=MAX($A2,$B2)

    to highlight the larger cell and (as a separate rule)

    =A2=MIN($A2,$B2)

    to highlight the smaller cell.

    enter image description here

    Note that in the case where both cells have the same value, they will both be either coloured red or green depending on the precedence of the rules. If the 'green' rule comes first,

    enter image description here

    it will look like this:

    enter image description here