Formating

[simonnimbus]

Can anyone please help - I have a column of dates and need to be able to colour the cells of dates that are more than either one or two years from the current date (obviously current date will change).
In a similar way I need different coloured cells for dates in the future that will then automatically change when they become current date and older.

I am slightly familiar with conditional formating but not that much!!

Many thanks in advance

Simon

 

[Barry Katcher]

If I understand you correctly, try the conditional formatting below:

Book1
	A	B	C	D
1	Date			CurrDate
2	1/1/03			3/22/04
3	1/1/04			HighlightA2:A7andConditionalFormat:
4	1/1/05			1)Cellvalue-greaterthan$D$2*730
5	1/1/06			Formatbluebackground
6	1/1/20			2)Cellvalue-greaterthan$D$2*365
7	1/1/07			Formatyellowbackground
Sheet1

 

[simonnimbus]

mmm, thanks but I'm not sure I get you. I did the conditional formating on the cells in my table and nothing happens.
Imagine you have 1000 pieces of electronic medical equipment that all need to be electrically safety testing at certain time intervals.
I have a long list of the model numbers, serial numbers etc etc and a last tested date - it is this date that I want to be shown in red to tell me that is is older than one or two years depending on the equipment.
When the equipment is then tested it will be given a date in the future and this cell should be green - until of course it reaches the cut ff date and it wil lturn red

Hope this makes sense

Simon

 

[Barry Katcher]

Simon, I'm not quite sure how you want to set up your spreadsheet. Rather than my guessing at your layout, why don't you download Colo's Utility (link at the bottom of any page in MrExcel) and use it to post an example of your spreadsheet, including past due, not yet due dates and future dates, and use regular formatting to set the colors to the way they should be. Then let's have a go at it.

Barry