Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
293 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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:

enter image description here

Output:

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...