Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Colouring Cells using a formula.

Options

Good Day All:

I would like a cell to change color (background color) based on a range(See Below). Has anyone used such a formula?

My Sheet: https://app.smartsheet.com/b/publish?EQBCT=906e1a0d8f8342e4baead18e6d0a76e4

Overall Rating Score

4.6 - 5.0 (Green)

3.6 - 4.5 (Light Green)

2.6 - 3.5 (Yellow)

2.0 - 2.5 (Light Red)

<2 on Any Objective / Criteria (Red)

Thanks

Comments

  • Robert S.
    Robert S. Employee
    Options

    Hello,

     

    This can be accomplished using conditional formatting rather than a formula. Here's a help center article with more information on conditional formatting (https://help.smartsheet.com/articles/516359).

     

  • Kal-El
    Options

    Conditional Formatting will not work with my current layout

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Since you have revoked the publishing of your example sheet, I can not imagine why Conditional Formatting won't work.

    Craig

  • Kal-El
    Options

    Hi Craig:

    I decided to use a formula because I only want Row 1 cells to have color. However, I am having problems adding colors to my formula.

    Published Link to New Sheet: https://app.smartsheet.com/b/publish?EQBCT=587e3b977fe34f409ca2991506151082

    My Formula: =IF([Total Points]1 >= 4.6, "Excellent", IF([Total Points]1 >= 3.6, "Good", IF([Total Points]1 >= 2.6, "Acceptable", "Unacceptable")))

    Desired Results:

    Excellent = Green

    Good = Light Green

    Acceptable: Yellow

    Unacceptable = Red

     

  • Kal-El
    Options

    Craig:

    I made some changes and wanted to add background color to Row 13 in addition to Row 1.

    I appreciate your help.

    Thanks

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 10/12/17
    Options

    Kal-El,

    You can't set a background color (or any formatting) via a formula. You can only do so using Conditional Formatting.

    However, all is not lost.

    1. Add a new column - name it something like "Use Conditional Formatting" (or something shorter). A Checkbox column would work.

    2. Check row 1 and 13.

    3. Set up a Conditional Formatting rule for each of the 4 types.

    You'll need to add an ADD condition (using the down-arrow icon after initial setup).

    Final result for Excellent should look something like the image below.

    (Note that there is no "greater than or equal to" in the conditional criteria list)

    I hope that helps.

    Craig

     

     

    ConditionalFormattingExample.png

This discussion has been closed.