Help with Multipole Conditional Formating

[kccarson002]

Hi Everyone!

I am hoping someone can help me here. I am at my wits end with this project.

So I have two different conditional formatting by formula rules in place.

The first is

=OR(H8="AUD-01",H8="AUD-02",H8="AUD-03A",H8="AUD-03B",H8="AUD-04",H8="AUD-06",H8="AUD-07",H8="AUD-05",H8="AUD-08",H8="AUD-09")

If it finds any of these codes it turns the corresponding L column Red. I.E. if AUD-01 is entered in cell H9 the cell L9 Turns Red.

The second formula is

=AND(L8="02 - Testwork")

What I want to happen is when the first formula finds any of these codes it turns the corresponding L column Red. I.E. if AUD-01 is entered in cell H9 the cell L9 Turns Red.

Then the user will use a drop down menu in the L Column to select what type of work it is. once they have selected the work type i.e. 02 - Testwork it should turn back to the SAME color as the table that it is in. I have tried the second formula with no formatting but am having no luck. I know its possible to change the color after an option from the drop down menu has been selected but how do you get it become the color that is used in the entire table??