Forum Discussion

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
Jan 16, 2019
I would highlight B2 to B200 and then apply the following formula

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

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

SergeiBaklan

Diamond Contributor

Jan 16, 2019

Perhaps to expand

(B1<TODAY())*(B1>0)

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
- SergeiBaklan
  Diamond Contributor
  Jan 16, 2019
  It excludes blank cells in column B
  - JenSmith
    Copper Contributor
    Jan 16, 2019
    Hi Sergei,
    
    Oh great, thank you. That will make a really useful addition! :)
    
    Thanks,
    Jen