How to change cells colours when an expiry date is coming to an end

[belly101]

I am trying to make a training spreadsheet that has the cells change colour depending on how near the training is to expiring.

Please can anyone help

Thank you in anticipation

[#2]

@belly101 

You can use Conditional Formatting for that. See for example Conditional Formatting 

If you would like more help, please provide some details about your setup.

[#3]

@Hans Vogelaar 

Hello, thank you for the information but I am still confused. I can change the colour manually but I want to be able to put a date into the cell (when the training has been completed) then have the cell automatically change from green to amber (when the training is one month from the annual date) to red on the expiry date. I have seen this somewhere but can seem to find how to do this.

Please can you help?

Thank you

Lee 

[#4]

@belly101 

Could you attach a small sample workbook without sensitive/proprietary information that shows your setup?

[#5]

Name Fire Fisrt Aid GDPR 1 Infection Lone Medication M&H Safeguarding
J. Smith 23.06.21 25.07.21
P. Jones

[#6]

@belly101 

I'm afraid that doesn't help much.

[#7]

I am sorry that I am such a neanderthal
I just want to make a spreadsheet where the cell automatically change when the mandatory training is nearing expiration and then again when they have expired,
Thank you for any help you can give
Lee

[#8]

@belly101 

You can attach a sample workbook to a reply simply by dragging the workbook file into the box below the space where you compose a reply.

 

[#9]

@Hans Vogelaar 

Hans

I have a similar question, and the answer may assist.

I would like to change the colour of a cell, depending on the context of another cell.

Possibly and if statement -

 if(A1>100, set Cell A1 fillcolor to green, set Cell A1 Fillcolor to Red) 

Now, I have used 'set Cell Fillcolor to ... and this is what I would like clarified, what should that be?

Thanks in anticipation

[#10]

@Laino63 

You write

---

I would like to change the colour of a cell, depending on the context of another cell.

but you then mention only cell A1...

[#11]

I also have a similar question. See if this helps us all get an answer.

In cell A1, if the number is equal to or greater then 1, then I would like cell A2 to turn orange.

Could you help with this example?

[#12]

@LJones20 

Select cell A2.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=A1>=1

Click Format...

Activate the fill tab.

Select orange.

Click OK, then click OK again.

[#13 · Accepted solution]

@belly101 Is this the one that you are looking for? Manual insert /change date will auto change the color.

[#14]

@MeiChan1314 

Where do I find the "Edit Formatting Rule"? 

[#15]

I found it under Conditional Formatting. It worked! Thanks so much!!!

[#16]

Thanks so much! This will save me so much time!

[#17]

Good one liked this!

[#18]

How can we highlight the dates one month in advance, say in order to send reminders to subscribers... Like today date plus 30 days, if matched in a list, the date falling after 30 days should be highlighted.