Conditional Formatting

[AndreaWilson]

Using my example below, I used conditional formatting that will fill in columns D - K based upon the data in columns A & B. Now I need to be able to change the color of the fill from the default to "green" when there is a "C" in column C (status). Thanking you in advance - Andrea



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Due Date[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]3/28[/TD]
[TD="align: center"]3/29[/TD]
[TD="align: center"]3/30[/TD]
[TD="align: center"]3/31[/TD]
[TD="align: center"]4/1[/TD]
[TD="align: center"]4/2[/TD]
[TD="align: center"]4/3[/TD]
[TD="align: center"]4/4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/28[/TD]
[TD]4/1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/30[/TD]
[TD]4/4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3/30[/TD]
[TD]4/3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[cb366374]

I'm assuming you used a formula for the first layer of conditional formatting (columns D-K). You can use the same method to test whether the value of $C2 = "C". Then you'll just need to move that rule to the top of the list so it takes priority over your other rules. You can do that by entering the conditional formatting "Manage Rules" box (Alt + O, D) and using the arrows next to the delete button, move the green rule to the top of the list.

 

[BlueHornet]

Given that you already have some conditional formatting rules that apply to columns D-K based on values in A-B, do you want your new format to override those? Do you want it to be the first condition to evaluate? the last? or the middle one if you already have two format choices?

It's simple enough to make a formula-based condition where =$C2="C", for example, to apply to the column/s in question, but the order in which that condition is evaluated will determine whether it is ever "seen", if the other condition/s is/are checked first.

 

[AndreaWilson]

Okay - using my example, the formula I used is as follows:

=(D$1>=$A2)*(D$1>=<$B2)

I don't understand how to add a new rule that will change the color of my fill from the default color to red based on information in column C.

Can someone please dumb it down for me.

Thanks! Andrea

 

[cb366374]

I think you should be able to use the formula that BlueHornet and I both suggested. You can have more than one conditional formatting rule applied to the same range. Excel will use the order in which they appear in the "Manage Rules" box to determine which format "wins." So while your formula above may be telling the cells to turn purple or something, if you place the =$C2="C" formula first in the list then it will override the purple and make it red. If column C does not contain a "C" then it will move to the next conditional format and you'll still see purple.

 

[AndreaWilson]

Thank you everyone! I finally have a spreadsheet that works!

Best, Andrea