SOLVED

Conditional Formatting Formula

Copper Contributor

Hi There,

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?

Trish

5 Replies

@trishmci 

 

Hi Trish,

Perhaps like

image.png

with yellow rule

=($J8<=TODAY())*NOT(ISBLANK($J8))

@Sergei Baklan Thank you, though that doesn't seem to work either.

Seemed perfect on your version though - i must be making an error with the formula somewhere.....

 

best response confirmed by trishmci (Copper Contributor)
Solution

@trishmci 

Please check attached

@Sergei Baklan thank you so much, it was driving me mad - that seems to work perfectly.  Not sure what i did, but really appreciate your help!

@trishmci , you are welcome, glad to help

1 best response

Accepted Solutions
best response confirmed by trishmci (Copper Contributor)
Solution

@trishmci 

Please check attached

View solution in original post