I have a legend with 5 specific names in a small range of cells which individually get highlighted based on conditions. 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.
If column C contains value "John", and column E contains value "PAYMENT PENDING", then color cell $A$7 YELLOW
If column C contains value "Alex", and column E contains value "PAYMENT PENDING", then color cell $A$8 YELLOW
If column C contains value "Smith", and column E contains value "PAYMENT PENDING", then color cell $A$9 YELLOW
If column C contains value "Tommy", and column E contains value "PAYMENT PENDING", then color cell $A$10 YELLOW
If column C contains value "James", and column E contains value "PAYMENT PENDING", then color cell $A$11 YELLOW
I also need a different color ORANGE when column E contains value "PAYMENT PROCESSING" for the same conditions above.
To clarify:
If column C contains value "John", and column E contains value "PAYMENT P PROCESSING", then color cell $A$7 ORANGE
If column C contains value "Alex", and column E contains value "PAYMENT P PROCESSING", then color cell $A$8 ORANGE
If column C contains value "Smith", and column E contains value "PAYMENT P PROCESSING", then color cell $A$9 ORANGE
If column C contains value "Tommy", and column E contains value "PAYMENT P PROCESSING", then color cell $A$10 ORANGE
If column C contains value "James", and column E contains value "PAYMENT P PROCESSING", then color cell $A$11 ORANGE
and then condition to undo color fill:
If column G contains any value, and column C contains value "John" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$7
If column G contains any value, and column C contains value "Alex" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$8
If column G contains any value, and column C contains value "Smith" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$9
If column G contains any value, and column C contains value "Tommy" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$10
If column G contains any value, and column C contains value "James" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$11
NXuHU0k.jpg
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. Keep in mind, I begin at row 16 and gradually fill out the rows down to row 300 throughout the day, so I want to be able to see the cells in the legend get conditionally highlighted only when I enter the values mentioned above.
I know this is a tall order, and I have asked in another forum but with no luck thus far... I'm hoping VBA might be the answer to my problem. Thank you so much for any help!
Bookmarks