Google-sheets – Conditional formatting based on another cell’s value

conditional formattinggoogle sheetsgoogle-sheets-formula

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell – C5. If C5 is greater than 80% then the background color is green but if it's below, it will be amber/red.

Is this available with a Google Sheets function or do I need to insert a script?

Best Answer

Note: when it says "B5" in the explanation below, it actually means "B{current_row}", so for C5 it's B5, for C6 it's B6 and so on. Unless you specify $B$5 - then you refer to one specific cell.

This is supported in Google Sheets as of 2015: https://support.google.com/drive/answer/78413#formulas

In your case, you will need to set conditional formatting on B5.

  • Use the "Custom formula is" option and set it to =B5>0.8*C5.
  • set the "Range" option to B5.
  • set the desired color

You can repeat this process to add more colors for the background or text or a color scale.

Even better, make a single rule apply to all rows by using ranges in "Range". Example assuming the first row is a header:

  • On B2 conditional formatting, set the "Custom formula is" to =B2>0.8*C2.
  • set the "Range" option to B2:B.
  • set the desired color

Will be like the previous example but works on all rows, not just row 5.

Ranges can also be used in the "Custom formula is" so you can color an entire row based on their column values.

Related Topic