Color a certain cell If two other cells meet a condition. Then undo cell color when a third cell meets a condition.

[aftbrah]

As the title suggests, this is my scenario:

I have cells $A$7:$A$11 and one of them must be colored a certain color if two conditions are met together in column C and E of the same row (for example C1 and E1)

If column C contains value "Q", and column E contains value "X100", then color cell $A$7 RED
If column C contains value "R", and column E contains value "X100", then color cell $A$8 RED
If column C contains value "S", and column E contains value "X100", then color cell $A$9 RED
If column C contains value "T", and column E contains value "X100", then color cell $A$10 RED
If column C contains value "U", and column E contains value "X100", then color cell $A$11 RED

I also need a different color ORANGE when column E contains value "X500" for the same conditions above.

To clarify:

If column C contains value "Q", and column E contains value "X500", then color cell $A$7 ORANGE
If column C contains value "R", and column E contains value "X500", then color cell $A$8 ORANGE
If column C contains value "S", and column E contains value "X500", then color cell $A$9 ORANGE
If column C contains value "T", and column E contains value "X500", then color cell $A$10 ORANGE
If column C contains value "U", and column E contains value "X500", then color cell $A$11 ORANGE

and then condition to undo color fill:

If column G contains any value, and column C contains value "Q", then undo color for cell $A$7
If column G contains any value, and column C contains value "R", then undo color for cell $A$8
If column G contains any value, and column C contains value "S", then undo color for cell $A$9
If column G contains any value, and column C contains value "T", then undo color for cell $A$10
If column G contains any value, and column C contains value "U", then undo color for cell $A$11

Basically, I just want the cells in $A$7:$A$11 to be colored conditionally and then undo coloring when a later condition is met.

Any help on this would be so gladly appreciated!

Thank you MrExcel.com for all the help you've already given me

 

[aftbrah]

Ok so I was able to use the following formulas by creating 2 rules for cell $A$7 in the conditional formatting manager:

=AND(C16="John",E16="Payment pending")

=NOT(ISBLANK(H16))

However this only applies to row 16. I cannot figure out how to create a range of rows where this can be applied.

Any help would be greatly appreciated

 

[tygrrboi]

Here is what I would do.

in B7 I would type the formula (regular formula)

=COUNTIFS($C$16:$C$300, A7, $E$16:$E$300, "Payment Pending", $G$16:$G$300, "")

and in C7 I would type the formula

=COUNTIFS($C$16:$C$300, A7, $E$16:$E$300, "Payment Processing", $G$16:$G$300, "")

Then I would drag these two cells down to A11.

If you do not want these numbers to be seen you can either make the font the same color as the cell color,
or give it a custom number format of

;;;

I think this is the most efficient way to determine who is pending, who is processing, and who is neither.


Now for the color.

Start with cell A7 and select down to A11. Now click the number formatting as you did before and put in the following.

For yellow highlighted cells,

=B7>0

for orange highlighted cells,

=C7>0


That should give you what you want.
IF a person is BOTH Payment Processing and Payment Pending (I do not know if this is possible) only one color will be highlighted. In the Custom Format Manager window you can see the hierarchy of your rules which will determine whether it shows yellow or orange in the event of both pending and processing. If you prefer it be the different color, just swap the order of these two rules. If you would like to assign a new color, you need to make a new rule:

=AND(B7>0, C7>0)


Sorry to get back to you so late. I got pulled away at work yesterday. Please let me know if you have any questions. <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Calibri}span.s1 {color: #0057d6}span.s2 {color: #006107}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}</style>

 

[aftbrah]

Thank you for the reply. This would unfortunately not work for me since I do have other values for Columns B1:B300 and C7:C15, and then I have to input the name of the person from C16:C300. I feel like VBA macros might do the trick, although I haven't a clue how to do it :/

Thank you again for trying, tygrrboi

 

[tygrrboi]

Those worksheet formulas do not need to be in B7:B11 and C7:C11 They can be in ZY7:ZY11 and ZZ7:ZZ11 or any cells that are empty. (they do not even need to be next to each other) Just make sure you are referencing those cells in your conditional formatting formula.

I do not know what you mean by having to put the names into C16-C300.... I assumed the names were already there.

 

[aftbrah]

I'm not sure I understood. Perhaps you could make an excel file and attach it so I can see how you did it?

Also, the names are not all there from C16-C300. Same with E16:E300 and G16:G300. This is a running document that starts from row 16 each day and I want to be able to conditionally highlight the Legend when the conditions are met temporarily and then undo when the other condition is met. Its similar to a cashier type of job (not actually, but similar). I will be making copies of this daily and using it from the start of the day until the end... that is what I meant.

 

[tygrrboi]

Sure thing. I tried to be as descriptive as possible.


https://drive.google.com/open?id=0B5M48TEIr1gudXFBaXM2UDEwTEk

 

[aftbrah]

AWESOME. This actually works perfectly for what I need. Thank you so much, tygrrboi.

Mrexcel.com has once again surpassed my expectations. Thank you again.

 

[tygrrboi]

Happy to help.