I have what is probably a really obvious conditional formatting formula query. Basically, i want a row to turn yellow if the date in one row (J) is today or earlier, but I then want to override this and highlight the row in a different colour once another row (U) is populated with text ("complete" the row will turn grey, "cancelled" the row will turn red - if blank or "in progress" it remains yellow).
I have set up the formulas (so =$U8="cancelled" format red / =$U8="complete" format grey / =$J8<TODAY() format yellow), and also set up the rule preference with the date at the bottom.
The issue is, the U8 rules do take precedence and highlight the rows in the correct colour but with the exception of the date reference cell in J which stays yellow. I tried changing it to an AND formula (to apply if U8 is blank or has other content) but then it just stays white when cancelled or complete is applied.
I am sure this is so simple, but if anyone could point me in the right direction?