SOLVED

# How to change the colour of a cell based on another value

Copper Contributor

# How to change the colour of a cell based on another value

Hi Everyone,

Happy New Year!

Looking for some help please. I have already applied conditional formatting to this spreadsheet to highlight improvement (green) or decline (red) in figures. However, because the rest of the cells appear red (because the value is <K), it is difficult to see clearly who is in red. Is there a way to either

1. stop the cells (M:Q) with no data turning red until a value is input

or (even better)

2. Make the name cell (A:B) fill red if the value of any M:Q < K ?

8 Replies
best response confirmed by RhiLew (Copper Contributor)
Solution

# Re: How to change the colour of a cell based on another value

Hi @RhiLew

Happy New Year too

or (even better) Make the name cell (A:B) fill red if the value of any M:Q < K ?

Conditional Formatting rule that applies to (with the above example) \$A\$5:\$B\$9:

``=COUNTIF(\$M5:\$Q5, "<" & \$K5)``

# Re: How to change the colour of a cell based on another value

To change the color of a cell based on another value in Excel, you can use conditional formatting. Select the target cell, go to the "Home" tab, choose "Conditional Formatting," and then select "New Rule." Pick "Format cells that contain" in the rule type, set your condition (e.g., equal to, greater than), and choose the formatting options, including the fill color. This way, the cell color will automatically change based on the specified condition in the adjacent cell, making data visualization and analysis more intuitive.

Also visit Get Dressed Shop.

# Re: How to change the colour of a cell based on another value

Oh wow - you are my hero! Thank you so much :)

# Re: How to change the colour of a cell based on another value

Thank you, this is what I have applied to the remaining cells. However, my knowledge of formulas is rather limited so I am a little stuck beyond this. The above reply is comprehensive though. Thank you

# Re: How to change the colour of a cell based on another value

@RhiLew You're welcome & Thanks for marking the solution (helps people who Search)

# Re: How to change the colour of a cell based on another value

I wonder if you might be able to help me further with the formatting of this same spreadsheet please?

I have been playing around with the formula to try and get it to make a name turn green if the number reaches >90.

Similarly, I need another colour if it falls <75.

I am not having much luck and I bet it is something very similar to the original formula that you gave me?

# Re: How to change the colour of a cell based on another value

Like this:

See the attached version of the workbook.

# Re: How to change the colour of a cell based on another value

Oh you are the best thank you so much! I can't believe the support of this community; so helpful; and so quick to respond - it's so appreciated.

(also kicking myself at how close I was to figuring it out myself!! :rolling_on_the_floor_laughing:)

Thank you again
1 best response

Accepted Solutions
best response confirmed by RhiLew (Copper Contributor)
Solution

# Re: How to change the colour of a cell based on another value

Hi @RhiLew

Happy New Year too

or (even better) Make the name cell (A:B) fill red if the value of any M:Q < K ?

Conditional Formatting rule that applies to (with the above example) \$A\$5:\$B\$9:

``=COUNTIF(\$M5:\$Q5, "<" & \$K5)``