Conditional Formatting

Hi Stuart,

Thanks for your question.

I had the same query a few years ago, and one of my fellow trainers here at Best STL showed me a solution. I hope it works for you, too...

Create another column (eg. H) and make it the formula: =$G1-TODAY()

Copy the formula down the whole of column H. Format the whole column to be Number format with 0 decimal places. You should find that the number in column H now represents the number of days left before the expiry date is up.

Now, to format the s.s, set up a Conditional Formatting on the s.s cell that you want to change colour.

If you haven't used Conditional Formatting before, select the cell you want to change the colour of, then go to Format menu -> Conditional Formatting.

In the drop-down box, choose 'formula is', and put: =$H1<90 in the text box next to it. Then click 'format' button and change format to text colour Red.

What you're saying is, if the value in column H is less than 90, highlight THIS value in red.

I just made it so the number of days remaining was highlighted as Red. But you can make any column/value you want to be red if you follow the formula above.

See attachment for more details.

If this answer resolves your query, please mark the question as 'resolved' (see below). Otherwise, please post a follow-up response to this post, and I will assist you further.

Regards, Rich

Hello Stuart

Thank you for your question.

Can you please clarify what is that you would like to come up in red - is it the cancellation date (when it is 3 months before the cancellation date); or the end date (when it is 3 months before the end date)?

Kind regards
Amanda