Forum Discussion

JenSmith's avatar
JenSmith
Copper Contributor
Jan 16, 2019
Solved

Conditional Formatting with Formulas - Help PLease?! :)

Hi All, 

 

I am trying to insert a forumula as part of conditional formatting into a tracker on a spreadsheet to highlight expired dates but only when text in another column meets certain criteria. 

(E.g. If the date in column B occurs in the past and the text in column F is equal to "PENDING" or "IN QUERY" - if column F contains any other content i do not want the formatting applied)

I know how to do each of these things seperately but for the life of me cannot work out how to stitch the 2 formula together to work as one (i thought i could use an IF formula to calculate but cannot get it to work). 

 

I would really appreicate any advice on how to make this work. 

Thanks in advance

Jen :) 

Conditional Formatting
excel
Formulas and Functions
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Jan 16, 2019
    I would highlight B2 to B200 and then apply the following formula

    =AND( B2<TODAY(), OR(F2="PENDING", F2="IN QUERY") )

12 Replies

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jan 16, 2019
    Try this (no need for the IF in conditional formatting)

    =AND( B1<TODAY(), OR(F1="PENDING", F1="IN QUERY") )
    • JenSmith's avatar
      JenSmith
      Copper Contributor
      Jan 16, 2019
      Hi Wyn,
      Thank you! I have just tried this and it seems to be looking at both columns for the answer now which is great but instead of highlighting just cell 'B1' (if PENDING or IN QUERY is present in F1) it is highlighting the whole of column B. Any ideas of how to adjust so that it only highlights the cell?
      Thanks again
      Jen

Resources