Help changing colour of cell depending on date entered

[dazlia]

Hi guys, wondered if someone could please help me?

I have a spreadsheet with a list of dates in column D. These dates are basically due dates on which a certain piece of paperwork is due to be completed for each client.

WE have a 4 week window in which to complete that particular peive of paperwork which can be completed 2 weeks prior to the due date all the way up to 2 weeks AFTER the actual due date.

Now, what i would liek to do is this.

1 - The day AFTER the due date I'd like the cell background (containing the date) to turn red
2 - 2 weeks PRIOR to the due date id liek the cell background to be orange
3 - anything prior to 2 weeks before the due date id liek the cell background to be green

to futher complicate things though .......

Once the paperwork is done and the date of completion is entered into column E id then like to have the cell background of the date in column D to turn back to clear and have no colour.

If anyone could offer me any help, baring in mind that im new to formulas etc then it would be very much appreciated

I have attached the spreadsheet so you can see what im talking about so so if its only a 2 minute job could you take a look please. If anyone DOES take on this challenge and decide to do it for me could you bear in mind that id liek the formulas to apply to the entire column so that when i add new rows the formula will be there already, if that makes sense

[MaczaQ]

Hello,

I have idea but I do not understand everything - probably because of poor English

I can try to help you but I need additional info about project.
Now I know that this formatting have to be used for all rows where is any data filled out, so your explanation could be for only 1 row.

for example we would like to see changes in sheet 1, row 1, column D
- when this effect should be done? [after filled out this cell ? or when the workbook is open ?]

I have to know action which file operator done for start reaction. Program is stupid and we have to “tell” him when he have to start action [formatting etc.].

Please write me in points what you will fill out and what result you want to get.
example 2:
1. I put date into D1 (no reaction)
2. next day I open workbook and background colour of cell D1 is changed automatically because one day left and date in E1 is empty
3. I put date into E1 -> cell D1 formatting have to be cleared
etc ...

I'm sorry for long post, I hope I showed what I need to help you.


Best Regards
Maciej

[dazlia]

Hi Maciej thansk for replying to me

Here goes....

1 - I open workbook
2 - I enter info into columns A, B and D (D is the date column i want to change colour)
3 - The colour of the cell in column D (the date column) changes depending on the date I put in it as follow :-
a) - The day AFTER the due date (in column D) I'd like the cell background (containing the date) to turn red
b) - 2 weeks PRIOR to the due date id like the cell background to be orange up to and including the due date.
c) - anything prior to 2 weeks before the due date id liek the cell background to be green

4 - Once that due date reaches I will fill out some paperwork with my client and I will then enter that date (the date i filled the paperwork in) into column E.
5 - Once I enter a date in column E I would like the cell background of column D in the same row (obvioulsy) to turn back to clear.

Does that make sense my freind?

Please see attached spreadsheet which you can download so you can see what im talking about. Many many thanks for trying to help here, much appreicated

[dazlia]

P.S Sorry i forgot to add this.....

Yes......when you open the spreadsheet each time the cell backgrounds will change colour based on todays date. In other words if column D has a date of date of 10th June in it and I opened the workbook today then that cell background would be orange.

If however I open the same spreadsheet on the 11th June then that cell background would have changed to RED becuase it is the day AFTER the due date in column D (10th June)

Hope that clarifies it for you

[realniceguy5000]

Hope this helps.

[MaczaQ]

Hello dazlia, I think solution wrote by realniceguy5000 is good so I will do not change anything. Sorry that I was delayed with my answer. Maybe next time I will be faster ;-)

Best Regards
Maciek

[dazlia]

Hope this helps.

Thanks Mike but that file you uploaded has no formatting in it and no coloured cells?

[dazlia]

Hello dazlia, I think solution wrote by realniceguy5000 is good so I will do not change anything. Sorry that I was delayed with my answer. Maybe next time I will be faster ;-)

Best Regards
Maciek

Hi Maciek,

could you please take a look anyway when you get a moment as the one posted by realniceguy doesnt work. The colours are wrong and dates that are PAST the due date are still showing as green when they should eb red

Everyones help is appreciated

[royUK]

You can do this with Conditional Formatting

[dazlia]

Hello dazlia, I think solution wrote by realniceguy5000 is good so I will do not change anything. Sorry that I was delayed with my answer. Maybe next time I will be faster ;-)

Best Regards
Maciek

Hi Maciek,

could you please take a look anyway when you get a moment as the one posted by realniceguy doesnt work. The colours are wrong and dates that are PAST the due date are still showing as green when they should eb red

Everyones help is appreciated

[dazlia]

You can do this with Conditional Formatting

Thanks Roy but this doesnt cover the fact that i need a cell colour to change back to clear when a date is entered in a seperate colum and im new to excel so really struggling.

[MaczaQ]

Dear Dazilia,

Finally I change a bit macro created by realniceguy5000.
If I understand whole condition it should be good tool for you

Of course don't hasitate to contact me for any more help.

Solution attached.

It should works if:
- workbook is open
- sheet is activate
- any change is made in column D or E for current activesheet

I optimalized whole surce to make it a bit shorter and easy.
Check Module1 and ThisWorbook surce.

Best Regards
Maciej