Google Sheets Conditional Formatting based on the cell directly NEXT to, across the entire row

Question

I am looking to show color changes based on decreasing values. I want to add conditional formatting to a row (row 9) based on the cell directly prior (B9<C9 I want to be green, C9>D9 I want to be red). I want an entire row to be conditionally formatted to show growth/decline based on the cell directly before. Is this possible? How can I do so? enter image description hereI have the formulas I want to use, but I don't know how to accurate apply them to the entire Row 9 (as of now I'm manually inputting the 3 custom conditions on each cell)

question from:https://stackoverflow.com/questions/65617709/google-sheets-conditional-formatting-based-on-the-cell-directly-next-to-across

Answer 1

You can use the following Custom Formulas from Range C9:9. Feel free to interchange the background color that will fit your needs.

• For Green (Increasing), =C9>OFFSET(C9,0,-1)
• For Red (Decreasing), =C9<OFFSET(C9,0,-1)
• For Yellow (Same), =C9=OFFSET(C9,0,-1)

OFFSET() lets you shift the rows/columns based on a reference cell. In this custom formulas, I get the previous cell value referenced to the current cell by setting the offset_columns to -1.

Sample:

Output: