Conditional formatting based on another cell s value

Question

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

User · Answer

I m disappointed at how long it took to work this out    I want to see which values in my range are outside standard deviation    Add the standard deviation calc to a cell somewhere  STDEV L3 L32  2 Select the range to be highlighted  right click  conditional formatting Pick Format Cells if Greater than In the Value or Formula box type   L 32  whatever cell your stdev is in    I couldn t work out how to put the STDEv inline  I tried many things with unexpected results

User · Answer

Basically all you need to do is add   as prefix at column letter and row number  Please see image below

User · Answer

I ve used an interesting conditional formatting in a recent file of mine and thought it would be useful to others too  So this answer is meant for completeness to the previous ones   It should demonstrate what this amazing feature is capable of  and especially how the   thing works   Example table    The color from D to G depend on the values in columns A  B and C  But the formula needs to check values that are fixed horizontally  user  start  end   and values that are fixed vertically  dates in row 1   That s where the dollar sign gets useful   Solution  There are 2 users in the table  each with a defined color  respectively foo  blue  and bar  yellow   We have to use the following conditional formatting rules  and apply both of them on the same range  D2 G3      AND  A2  foo   D 1 gt   B2  D 1 lt   C2   AND  A2  bar   D 1 gt   B2  D 1 lt   C2    In English  the condition means  User is name  and date of current cell is after start and before end  Notice how the only thing that changes between the 2 formulas  is the name of the user  This makes it really easy to reuse with many other users   Explanations     Important  Variable rows and columns are relative to the start of the range  But fixed values are not affected    It is easy to get confused with relative positions  In this example  if we had used the range D1 G3 instead of D2 G3  the color formatting would be shifted 1 row up  To avoid that  remember that the value for variable rows and columns should correspond to the start of the containing range   In this example  the range that contains colors is D2 G3  so the start is D2   User  start  and end vary with rows -  Fixed columns A B C  variable rows starting at 2   A2   B2   C2  Dates vary with columns -  Variable columns starting at D  fixed row 1  D 1

User · Answer

One more example   If you have Column from A to D  and need to highlight the whole line  e g  from A to D  if B is  Complete   then you can do it following    Custom formula is      B  B  Completed   Background Color      red  Range                 A D   Of course  you can change Range to A T if you have more columns   If B contains  Complete   use search as following     Custom formula is     search  Completed   B  B   Background Color      red  Range                 A D

User · Answer

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     There is no mention that B5 contains any value so assuming 80  is  8 formatted as percentage without decimals and blank counts as  below    Select B5  colour  amber red  with standard fill then Format - Conditional formatting     Custom formula is and     C5 gt 0 8   with green fill and Done

User · 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 gt 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 gt 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

[google-sheets] Conditional formatting based on another cell's value

Examples related to google-sheets

Examples related to google-sheets-formula

Examples related to conditional-formatting