Forum Discussion
Conditional formatting multiple cells based on the TEXT in another cell in MS Excel.
I am trying to make a group of 7 cells conditional format based on the TEXT input into another cell.
In my example below, I want to make D5 through K9 to reflect a certain color based on the response in K5. Ie., if K9 is DISPATCHED, I want D5 through K9 to change color to pink. I cannot get it to work and out love some assistance. Thank you.
3 Replies
Select D5:K9. The active cell in the selection should be in row 5, for example D5.
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=$K5="DISPATCHED"
Click Format...
Activate the Fill tab.
Select pink as fill color.
Click OK, then click OK again.HansVogelaar thank you, that worked. I do not know why when I typed it in using the same formula (I thought?) it did not work. Anyhow. Thank you.
Now, if I wanted to get really stupid, could I do the following to the same spreadsheet. Could I make it so that it knew only to change the color of the LOAD STATUS (K5) and whichever cell represented ONLY TODAY and YESTERDAY (G5 & H5, for instance using today as the example).
It works fine for my liking as is, my boss on the other hand thinks it is to confusing because I have it showing the whole week at whatever the current load status is and he thinks others would be confused by it.
Select D5, then select Conditional Formatting > Manage Rules...
Select the rule, then click Edit Rule...
Change the formula to
=AND(OR(D5=TODAY(), D5=TODAY()-1), $K5="DISPATCHED")
Click OK.
Note that there is no $ before the column letter D, because we want Excel to change it to E, F, ... as needed.
If you want K5 to be pink even if none of the dates is today or yesterday, create a separate rule for K5:K9 with formula =$K5="DISPATCHED".
