Share via

Using Conditional Formatting to Color a Cell Doesn't Work

Anonymous
2022-09-30T20:02:35+00:00

Hello - I'm trying to use the formulas below to color a column of cells that contains a formula but it's not working:

If the results of the formula in Column E is less than or equal to 8 - I want the cell to be Green (RGB 0,176,80)

If the results of the formula in Column E is between 8 and 9 - I want the cell to be Yellow (RGB 255,255,0)

If the results of the formula in Column E is greater than 9 - I want the cell to be Red (RGB (255,0,0)

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2022-09-30T20:31:31+00:00

    You shouldn't include RGB in the formulas, it is not an Excel function. Actually you don't need to use rules of type formula at all.

    Select E6:E12

    Set the fill color to green. This will be the default.

    Click Conditional Formatting > Manage Rules...

    Delete the existing rules.

    Click New Rule...

    Select 'Format only cells that contain'.

    Leave the first drop down set to 'Cell Value'.

    Select 'greater than' from the second dropdown.

    Enter 8 in the box next to it.

    Click Format...

    Activate the Fill tab.

    Select yellow.

    Click OK, then click OK again.

    Repeat these steps,, but with 9 in the box and red as fill color.

    Finally, close the Conditional Formatting Rules Manager by clicking OK.

    2 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,350 Reputation points Volunteer Moderator
    2022-09-30T20:43:05+00:00

    Hi DeeCee. I am an Excel user like you.

    Everything that I have learned about Conditional Formatting formulas is that the formula must have either a TRUE or FALSE result. In your first formula if the logical test, E6>9 is met, then the result of the formula is "rgb(255,0,0)"--not TRUE, so the conditional formatting is not activated. The AND function is superfluous but not a problem if you want to see consistency within the formulas. Use:

    =IF(AND(E6>9),TRUE)

    Then use the format button to choose the red color. Of course, the other two formulas must also, in the same manner, produce a TRUE or FALSE result.

    On your second formula, the logical test has an issue unless you are going to have decimals. A whole number cannot be both >8 and <9. That logical test will only identify 8.1-8.9.

    Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

    Rich~M

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2022-09-30T21:22:23+00:00

    Worked like a charm - thank you so much! I even added another rule for cells that are blank - whose formula would return a zero but this spreadsheet must be set to not show zeros for formulas. I appreciate the help - have a good weekend!

    By the way - I'm surprised the RGB isn't an Excel function - it sure let me enter it like it was! Sheesh!

    0 comments