Conditional formatting based on value of adjacent cell-CodePudding

I have a bunch of information that I am trying to highlight based on the value of the information in the cell to the right of the current cell. I want it to be highlighted green if the value is greater than the value to the right, and red if lesser than the value to the right.

I have so far been unsuccessful. I tried a recommendation from another post that said "=A1<B1" to highlight green and vice versa, but sadly, that did not work. A screenshot of what that did can be seen here:

Locking my columns gives me this result:

Another attempt:

Response to @Cyril:

I hope you are able to help. Thanks in advance!

CodePudding user response：

When using conditional formatting, you need to be very careful about what gets fixed.

This formula, from your post, will affect the entire ranged acted upon:

"=A1<B1"

If you want to affect an entire row, then you need to lock the column in your formula, allowing the row number to be dynamic, e.g.:

"=$A1<$B1"

Locking a row number does not affect the range the same name, due to the comparison still being cell to cell, only the result is output over an entire range, e.g.:

If you want to have a cell:cell comparison over multiple columns, you will need to have multiple conditional formatting:

Edit:

Turns out we were all making the same typographical issue in our responses.

A single formula can work where we use the correct range references:

Note that in the above pictures, we had affected Column A, meaning that the carried-forward formula for conditional formatting was:

'For column A:
=A1>B1
'For column B
=B1>C1

So the last column is comparing a number versus nothing (0), which is why it kept showing up as all "green".

This was noticed when looking at Prema's post, and the "Another attempt" in Jacob's post, which had me redo the overall. Note that we corrected in the comments section the formula and affected range being the same, where a formula for =A1>B1 affecting $A$2:$B$4 will show an undesired result.

CodePudding user response：

I'm not sure if you can realise it with just one formula: in my opinion you need one formula for the green colour and one for the red colour. I've done it in the following way: (according to the Chinese, a drawing says more than a thousand words :-) )