Conditional formatting based on another cell’s value

by

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.

More Related Contents:

  1. ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
  2. Google Sheets importXML Returns Empty Value
  3. Get the last non-empty cell in a column in Google Sheets
  4. How to stop / freeze / pause volatile RAND / RANDBETWEEN / RANDARRAY?
  5. How to SUM duration in Google Sheets?
  6. ArrayFormula and “AND” Formula in Google Sheets
  7. ARRAY_LITERAL, an Array Literal was missing values for one or more rows
  8. Query is ignoring string (non numeric) value
  9. Repeat whole row N times based on column value in Google Sheets
  10. I get Formula parse error with all formulas
  11. IMPORTXML and right XPath from Bloomberg price [duplicate]
  12. What factors determine the memory used in lambda functions?
  13. How to list all permutations without repetition?
  14. How to use arrayformula with formulas that do not seem to support arrayformulas?
  15. Repeat each row N times in Google Sheets
  16. How to create all possible pair combinations without duplicates in Google Sheets?
  17. Col BY causing parse error in Google sheets query
  18. Count cells that contain any text
  19. How to make a range repeat n-times in Google SpreadSheet
  20. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  21. How to create a runing total formula that expands automatically when new rows are added to my spreadsheet [duplicate]
  22. How to Autofill last row with formula, when data is received from IFTTT on the last row?
  23. How to compare dates or date against today with query on google sheets?
  24. Download link for Google Spreadsheets CSV export – with Multiple Sheets
  25. Stacking multiple columns on to one?
  26. New Google spreadsheet Concatenate limit 50000 characters
  27. Google Sheet use Importxml error could not fetch url [duplicate]
  28. TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
  29. Google Sheet API v4
  30. How to create INDIRECT array string of multiple sheet references in Google Sheets?

Leave a Comment