#### 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.

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?

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

• Employee

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).

• Conditional Formatting will not work with my current layout

• ✭✭✭✭✭✭

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

Craig

• 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

• Craig:

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

Thanks

• ✭✭✭✭✭✭
edited 10/12/17

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

This discussion has been closed.