Forum Discussion
I need to change the color of all cells in a row based on the data in a single cell
Hi all,
I'm making a tracking sheet for tasks in progress. I have a column for status, where I have conditional formatting on to change the colors based on certain data, like 'ON HOLD' is red, 'DONE' is green, 'IN PROGRESS' is yellow, etc.
What I want to do is set all relevant cells in any given row to change to the same color, so if the tracking cell is set to yellow, then I want the other cells in that row to also turn yellow.
I feel like there's probably a very easy way to do this, but damned if I can find it.
On a separate note, I've been trying to post this question for several hours now. We all know what company this is; I'm not expecting miracles here. The issue comes up when I try to type in my question, wherein the site sooner or later pops up a message that it's saved the data so far; when I try to post the message, another popup informs me that it failed to post, and I can't post until I fix the highlighted issues, which it fails to highlight, but the only thing that's still popped up at that point is the message that it's saved the data that's been input thus far - clearing that, and trying to post then comes up with a different error, saying that I've had the audacity to try to post twice in less than five minutes, despite the fact that the site has never permitted me to post anything because it can't stop failing.
If someone could perhaps tweak the site's coding so it can fail more efficiently, that will make the end-user experience less harrowing.
I apologize for the sarcasm, but I'm genuinely annoyed at this point at how much of my time this defective website has wasted.
Let's say your data are in rows 2 and below, and the status is in column K.
Select those rows. The active cell in the selection should be in row 2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$K2="ON HOLD"
Note that there is a $ before the column letter K but not before the row number 2. This is essential.
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.Repeat these steps, but with the formula
=$K2="DONE"
and green as highlight color.
Etc. - one rule for each status.
10 Replies
Let's say your data are in rows 2 and below, and the status is in column K.
Select those rows. The active cell in the selection should be in row 2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$K2="ON HOLD"
Note that there is a $ before the column letter K but not before the row number 2. This is essential.
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.Repeat these steps, but with the formula
=$K2="DONE"
and green as highlight color.
Etc. - one rule for each status.
Greetings Hans, Very good formula, I appreciate this, However, I ran into a problem where I don't know how it happened.
When I completed this function, and input my data, it not only changed the affected line green, but ALSO changed the line below it green too.
I've experienced this in a different sheet where it didn't really matter, but I'd like to find the "why" and a resolution. Any input?1) Are you sure that you used a formula such as
=$D2="ON HOLD"
and not
=$D$2="ON HOLD"
2) Does your range perhaps contain merged cells?
- Hi Hans - I tried that yesterday after googling some how-to's - for some reason it hasn't worked for me.
gah - I just got it to work - I must have forgotten the quotation marks yesterday.
Thanks!!
