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....
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") )
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
    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Jan 16, 2019

      Hi

      Can you attach a screenshot of what you mean?


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

        Hi Wyn, 


        I've added a snapshot of my spreadsheet below. 

        My goal is for dates in column B (INV DUE DATE) to be highlighted red but only if the answer in column F (STATUS) reads PENDING or IN QUERY. If column F reads PAID I do not want the formatting added to B. I hope that makes sense? 

        When adding the conditional formatting to columb B, i start by selecting the whole column, then opening the conditional formatting options to apply the formula  - is this correct? 

         

        Sorry if i'm not explaining this well, i'm not the best with formulas containing more than one function! haha 

         

        I really appreicate your help. 

        Thanks

        Jen 

         

         

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 16, 2019

      Perhaps to expand

      (B1<TODAY())*(B1>0)
      • JenSmith's avatar
        JenSmith
        Copper Contributor
        Jan 16, 2019
        Hi Sergei,

        Thanks for your input - you'll have to excuse my very basic knowledge of excel formulas, but what result would this formula acheive?

        Thanks you
        Jen

Resources