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

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.

CodePudding user response：

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.

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,

it will look like this:

CodePudding user response：

Conditional formatting is almost its own little science within Excel. It may be more useful to find youtube tutorials on the topic than depend on a text explanation here. But the central theme is this.

You will use location locking (the dollar sign or F4) in front of the letters so that any cell to which the format is applied knows you specifically mean columns E and F, for instance.

Example: Assume your first row goes from A5 to M5, and the condition values are in E5 and F5.

I find it easiest to format one row with the rules I want, test them, and then use the format painter or copy -> paste format along with careful use of $ locking.

1. Drag over and select the entire row of cells A5:M5
2. Conditional Formatting -> New Rule -> Use a formula to determine which cells to format
3. In the formula field enter =$E5>$F5. Excel gets weird and often inserts double quotes. If you save the rule and go back in, it may say ="$E5>$F5" and if so delete the double quotes.
4. Click Format and create the cell format you want.
5. With A5:M5 still selected, add another rule and format for ="$E5<$F5"

The $ sign works the same way as it does in a formula. All of the columns get their format based on columns E and F, but all of the rows base their formula on the E and F values in that same row.