Forum Discussion

Michelle Rorer's avatar
Michelle Rorer
Copper Contributor
Jan 23, 2018

Help with formula to color code cell based on cell value

Hello,

 

I am looking at a large spreadsheet and need some help with conditional formatting.  I believe I need some kind of If, then formula, but need help coming up with the right one.  Basically, I want it to change a cell either red or green based on whether or not the cell is greater or less than the values in a different column.  

 

Thanks in advance for your help.

 

 

  • Hello,

     

    let's assume that you want to use conditional formatting in column A and the values that you want to compare with are in column B. Here are the steps:

     

    1. Select the cells in column A, let's say A2 to A10

    2. click Conditional formatting > New Rule > Use a formula to determine

    3. in the formula box enter a formula along these lines

     

    =$A2>$B2

     

    4. click the format button and select green.

     

    Repeat the steps and use the formula

     

    =$A2<$B2

     

    set the format to red.

     

    In a conditional formatting rule with a formula, you don't need an IF() statement. The formula is the condition and it must return either TRUE or FALSE. When it returns TRUE, the format is applied.

     

    Note how there are $ signs in front of the column letter to lock the formula to these columns, but no $ signs in front of the row number, so the formula adjusts to the current row number in all rows that the format applies to.

     

    Does that help?

     

     

     

Resources