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

[RhiLew]

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 ?

Thanks in advance! 

[#2 · Accepted solution]

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)

[#3]

Visit This article: https://techcommunity.microsoft.com/t5/excel/i-need-to-change-the-color-of-all-cells-in-a-row-based-...

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.

[#4]

Oh wow - you are my hero! Thank you so much 🙂

[#5]

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

[#6]

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

[#7]

@Lorenzo 

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? 

Thanks in advance!

[#8]

@RhiLew 

Like this:

See the attached version of the workbook.

[#9]

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!! 🤣)

Thank you again