Can anyone help me edit this macro - colour change depending on date

Hi folks,

I currently have a spreadsheet that someone else setup for me a while ago and basically in column D i think theres a macro that changes the background of the cells in column D depending on the date. I think its setup at the minute so that when i enter a date its GREEN up until 2 weeks prior to that date. When it hits 2 weeks before that date it then turns orange and then when it actually hits that date it becomes overdue and therefore turns red.

What Id like it do do now is this please?.....

1 - Firstly I need to delete column C as i no longer need that.

2 - I enter a date (this is a date when a patients next prescription is due) and if the date i enter is more than 10 days BEFORE todays date I want it to change the cell bacground to green. In other words a black cell is white and as soon as i enter any date it turns green by defualt.

3 - When its 10 days prior to todays date i want to turn orange

4 - When its 5 days prior i want it to turn red

I'm sorry to be a pain, im sure its only a 10 second job if you know how to do macros but im totally clueless im afraid. Could someone spare me a few minutes to edit this spreadsjheet for me please? Would be appreciated. thank you

Hiya,

This seems fairly straightforward. But, to get some clarity, I have a few questions.

1. If we delete column C, D becomes C so the macro needs to point at that. Not a problem - but just checking you're OK with that.

2. The date is when the prescription is next due. If this date is BEFORE today's date, it is late. That shouldn't go green, surely? I don't understand, "a black cell is white as soon as I enter any date it turns green by default". Not sure what your requirement is there.

3. Similar to 2, I guess. Do you mean when the date is ten days away from today's date?

4. Ditto ... today is 5 days prior to the entered date, not the other way round?

Just checking ...

Steve.

Thanks Steve, My fault for not being clear


1. If we delete column C, D becomes C so the macro needs to point at that. Not a problem - but just checking you're OK with that.

Yes I no longer need column C and understand that column D will become C once deleted and you'll no doubt have to reflect that in the macro somehow so that it then works on column C.

2. The date is when the prescription is next due. If this date is BEFORE today's date, it is late. That shouldn't go green, surely? I don't understand, "a black cell is white as soon as I enter any date it turns green by default". Not sure what your requirement is there.

My fault. Basically I wanted it green by defualt just to represent the fact that i didnt have to worry about it as it was prior to 10 days before the due date that would be in that cell. So basically the date i am entering is when someones prescription is next due. If its green then it means that it 'ok' and the closer it gets to that due date the cell will then turn orange 10 days before that date and subsequently red when its 5 days before that date

3. Similar to 2, I guess. Do you mean when the date is ten days away from today's date?

Yes mate so if the date in a cell was 10th March 2012 it would be green by default then turn orange on the 1st March and then red on 5th March

4. Ditto ... today is 5 days prior to the entered date, not the other way round?

see above mate

Thanks again

P.S Just to clarify something that i missed in my last reply:-

a black cell is white as soon as I enter any date it turns green by default". Not sure what your requirement is there.

That should have read BLANK cell not BLACK cell

So basically an empty cell is white then turns green when i put a date in. Unless of course (in theory) I was putting a date in that cell today and the date i was entering was 9th March in which case it would be ORANGE etc etc.

Try this - I've had a go with it on June & August tab - seems to work OK.

Steve.

Steve, thats excellent mate thanks so much your a star!

One more small thing though if its ok. I'm only using 1 tab so ive deleted everythign else and now left with the august tab and im only using columns A to C. When i deleted colum D and E it still highlights those column. What part of the macro do i need to edit so that it only puts colours in colums A to C please?

I know how to edit the macro I'm just not sure which part tells it to highlight the 5 columns as apposed to the 3 im using.

We are nearly there, sorry to be a pain and thanks again mate

Not a problem!

The colouring is done by defining a range and changing the Interior.Color of it.

So, the code:

Please Login or Register  to view this content.

defines the range with x being the row currently being looped through, and 5 being the column boundary. Amend that to 3 for column C:

Please Login or Register  to view this content.

And that should be fine.

At the moment, it occurs three times in the code - that's a bit poor. If you add it just after the first line:

Please Login or Register  to view this content.

but before the conditional IF statement, that'll tidy the code up a bit. You can then remove all other lines that "Set" the range.

Hope that helps!

S.

P.S. I'm still struggling to get it to erase the colour when you delete a date. This is because it sets the looped range from the top of the sheet; there's a better way of doing that if this is something that gets in the way?

Actually, it's annoying me ...

Change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

That works better.

Steve.

And, rather than filling the cells with White when the date is deleted, or a non-date is entered, change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

That's a bit nicer too.

I'm happy now!

S.

For simplicity; here is the final code:

Please Login or Register  to view this content.

Hi Steve,

Wow you are patient Thanks mate.

It doesnt work though I replaced teh entire code with the one you posted and it still highlights over 5 columns. Then when you delete a date it DOES make the first 3 columns turn white but colour still remains in columns D and E.

I'll post it up so you can see.

I bet you wish you'd never offered to help now lol

Appreciated though

DateTest-from steve-new.xls

I think that is just a hangover from the old code extending into column 5.

If you change the dates, only the first three columns are affected. Best just highlight column D & E and select No Fill - I think that should solve the problem?

Let me know ...

S.