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]

bump

 

[tygrrboi]

How about this for conditional formatting in A7

red:

=AND(E7="X100", CODE(C7)>80, CODE(C7)<=85, G7="")


orange:


=AND(E7="X500", CODE(C7)>80, CODE(C7)<=85, G7="")



Then change the range for those two rules to A7:A11 instead of just A7

 

[aftbrah]

I tried it but it returns with FALSE in the cell. No changes to color...

 

[tygrrboi]

That formula doesn't go in the cell. It goes in conditional formatting. Click cell A7 and click conditional formatting in your ribbon. Then in the conditional formatting menu find the option to use a formula. Then put it there. When you click manage conditional formats you will see the cells that the formatting applies to. Change that range from just A7 to A7:A11 or wherever you need it to finish in column A.

 

[aftbrah]

That formula doesn't go in the cell. It goes in conditional formatting. Click cell A7 and click conditional formatting in your ribbon. Then in the conditional formatting menu find the option to use a formula. Then put it there. When you click manage conditional formats you will see the cells that the formatting applies to. Change that range from just A7 to A7:A11 or wherever you need it to finish in column A.

Oh i see. It works actually thank you.

I was wondering how I would be able to do this for values other than "Q" or "S" etc. Those values won't be the real values I will use, I just chose those letters as a test. The actual values will be different phrases that will go for their corresponding cell between A7:A11.

 

[aftbrah]

I'm also looking to apply this to entire columns, for each row. I want to be able to apply these conditions at certain rows down the columns when need be.

 

[tygrrboi]

Are your highlighted values (that will replace Q, R, S, etc) stored somewhere in a list? If so where? If not could you give me one or two examples of what they might be so I could give you an idea about how to do this?


What do you mean entire columns? Excel has 1,048,576 rows... I would be surprised if you intend to use all of them. I would suggest assigning your conditional formatting range to something large, but not the entire column as conditional formatting has potential to add unnecessary weight to a document when you don't need it. Could you assign it to A7:A10000?

In case you were unsure, you assign the range for conditional formatting like this. Open your "Manage Conditional Formats" menu and click the dropdown box to show your formats for the entire sheet. You should see your formula in there and a space that says something like "Applies to" This is where you would put the range of all cells you want to follow that coloring rule. (A7:A10000 for example). Here is a picture that might help.

 

[aftbrah]

Okay so the range of cells which are highlighted are only A7:A11. However, I have rows from column A to G, that go all the way down to row 300. The columns I'm only concerned about for conditionally formatting A7:A11 are columns C16:C300, E16:E300 and G16:E300. Below is an illustration of what I mean:

The 5 names are constant and they are located in the Legend A7:A11. However, only when the condition of the Name and "payment pending" are in the row, then I want their respective cell to be highlighted yellow in the legend or if "payment processing" then highlight orange. I want to to undo the highlighting of that specific cell only when the word "complete" is filled in Column G. This gets repeated later down the table, so I want to be able to repeat highlighting their name in the legend A7:A11 whenever they make another "payment pending" or "payment processing" and then undo highlighting when "complete".

I hope I made the explanation clear this time and thank you so much for any help you can give, I really appreciate it!

 

[aftbrah]

bumping thread for advice