Complex Conditional Formatting Based on Multiple Scenarios

Hi guys,

I've got a bit of a complex problem in Excel and was wondering if anyone would be able to help? It's essentially a conditional formatting rule but one that requires multiple changes based on certain criteria being met. It's easier to show than explain, so I've attached a sample file.

Basically, I'd like to be able to insert a date into the "Stock Check Date" and then have any stock which has not arrived within a month to be highlighted orange, and any stock that has arrived but not been sold within a month to be highlighted in red. I'll then be able to apply a filter and custom sort the data as need be.

The problems I've encountered are:

- I can get the row to highlight orange by subtracting the check date from the purchase date and telling it to highlight if this value is greater than 31 days, but it will still be highlighted even when the stock has sold, which I don't want.

- If stock was highlighted in red as not selling after a month but then does sell, how can I tell Excel to remove the highlight? Currently, the area will still stay highlighted red even if the stock was eventually sold. I only want it to be red if it hasn't sold within a month but then go away when it eventually did sell.

- Same as above, but for the orange highlight. Even if the stock took longer than a month to arrive but eventually did arrive, how can I tell Excel to remove the highlight? At the moment, it will still stay orange even when the stock did arrive.

I tried tinkering with IF formulas and conditional formatting but I can't get it exactly right. I don't think I'm using the right formula and I'm not sure if this is even possible to do, but would somebody be able to help?

Many thanks.

Originally Posted by nflsales

see the attached file

Wow, that is perfect and is exactly what I was looking for! Thank you so much, it's really appreciated!!

Originally Posted by Vikas_Gautam

There is a difference in what you are saying and what you mean...

I mean, How will excel be knowing to remove Conditional Formatting as you have already told him to stay red if not sold within a month..?

I guess we are missing something here that is CURRENT DATE.

I guess you want to highlight it red when (CurrentDate - ArrivedDate) > 30
and Red color should vanish when a date is put in Date Sold Column... Right..?

nflsales managed to solve it for me, but thank you for having a look into it.