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.
Here is a simple spreadsheet that is more complex implementing @Rubén's solution:
B:B has the following custom formulas:
=B2>=A2for green (if the cell on the right is greater than or equal to the cell on the left)
=B2<A2for 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.
2 should be green, and
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
2increments. 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
2value 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
MATCHfunction, but I wasn't able to make this work.