How to Apply Conditional Formatting Based on Adjacent Cell Value for Entire Column?

Question

I am looking to apply conditional formatting to all cells in a column (excluding the header) where a colour will be applied depending on if its value is greater, equal, or less than the adjacent cell.

Simple example:

	A	B
1	100	200
2	50	150
3	80	20
...	...	...

Here is a simple spreadsheet that is more complex implementing @Rubén's solution:

https://docs.google.com/spreadsheets/d/1pl_UOyyE8r-JOcGWM2zsA2fFAbuvH3EUg7YnonaTZqY/edit?usp=sharing

Range B:B has the following custom formulas:

• =B2>=A2 for green (if the cell on the right is greater than or equal to the cell on the left)
• =B2<A2 for red (if the cell on the right is less than the cell on the left)

If the value in column B is greater than or equal to the value in column A, colour the B cell green. If the value in column B is less than the value in column A, colour the B cell red.

So rows 1 and 2 should be green, and 3 red.

I am familiar with how to do this individually on a per cell basis, but if I have hundreds of rows and need to do this over multiple columns I don't want to manually do this hundreds of times. It looks like I need to use the Custom formula is on Conditional formatting rules, but I can't figure out how to target a relative cell by position.

---

What I've tried:

• Conditional formatting on one cell such as Great than or equal to $A2, then dragging this row down hoping the 2 increments. It doesn't. This also wouldn't work in scenarios where there are unique formulas or static values in the cells without going back and updating the cell values.
• Using the Custom formula is, but unable to find the correct function to pair it with to again make that 2 value increment. Looked at MATCH and other descriptions of functions and didn't see anything suitable for dynamic cell.
• Looked at using the Script Editor, but this didn't seem right and seemed like overkill for what I feel is pretty simple.
• Googling, but somehow not finding anyone who's wanted to do this before. Stack has questions that are close, such as Conditional Formatting based on other column that also use the MATCH function, but I wasn't able to make this work.

Answer 1

Please use the following formula

=B2>=A2 for green (if the cell on the right is greater than or equal to the cell on the left)

=AND(B2>=A2,B2<>"")

=B2<A2 for red (if the cell on the right is less than the cell on the left)

=AND(B2<A2,B2<>"")

Answer 2

Either the Apply to range or the formulas are wrong.

• Instead of B:B use B2:B

OR

• Instead of B2>=A2 it should be B1>=A1
• Instead of B2<A2 it should be B1>A1

---

1. Apply to range you might select the whole column B
2. Use a custom formula with relative references i.e. B1 instead or $B$1

First rule

Second rule

Related

• How to conditional format in Google sheets based on cell directly above it?
• Google sheets conditional formatting relative to another cell in the same column