Colouring Cells using a formula.

Kal-El

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

Robert S.

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

Conditional Formatting will not work with my current layout

J. Craig Williams

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

Craig

Kal-El

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

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

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