3

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.
3
3

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<>"")

enter image description here

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

2
  • Thanks for taking the time to answer. It seems very simple, and I tried it with my spreadsheet and it didn't work. I made a new spreadsheet that was simplified and tried this again and it still doesn't work. I maybe am not explaining the problem well, but hopefully this spreadsheet is self-explanator if you'd like to take a look and see: docs.google.com/spreadsheets/d/… – gpresland Apr 9 at 1:11
  • 1
    After playing around with your suggestions I was able to make it work and understand it now. The thing throwing me off was the 2 in the B2 and A2 comparison not changing with the rows even though it was working. I was feeling I needed to do something to denote it being dynamic. – gpresland Apr 9 at 2:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.