create a macro to alert me if today's date is within 5 days of expected delivery date

I have a worksheet that has a sent date and expected delivery date I need create a macro that will alert me if today's date is within 5 days of expected delivery date. please help

Hi, and welcome to the forum.

No need for a macro. Just name the delivery date cell "deliverydate" and the today date cell "todaysdate", then use the following function.

Please Login or Register  to view this content.

You could also usefully put a conditional format on the cell that contains that IF() function, and have the cell turn say red if true.

HTH

thanks for the welcome & actually I do need a macro. I give a bit more detail. I have a workbook that has po# date entered. info that goes w/ order. then a column for del. date & column for recv'd. what I the macro to do is if the del. date is w/in 5 days of today and the recv'd column is empty to turn it red so someone can be alerted since the work book is opened everyday. I pretty much have the rest of the macro figured out but not sure how to word w/in 5 days of today.

cf as
=AND(ISBLANK(B2),TODAY()-A2<=5)
wher b2 is rec date and a2 deliv date

conditional formatting not working the way i need it to any other suggestions i can plug into a macro

I have 9 columns the third is the date the po is entered the 8th is the expected delivery date. the 9th is the received date. I need the entire row to be highlighted red when the delivery date is within 5 days of today when the received date is blank. conditional formating does not do that

I just attached a sample to my previous post. with this workbook. I need the row to be highlight if the del. date column is within 5 days of today and the recv'd column is empty. all other information isn't vital. I have used real dates to help

Thanks yeah I took out all conditioning formating so that it would be cleaned. I did try that and it didn't work. all it did was fill in the empty cells in h and I

here is a sample with what i want done automatically. I did it manually w/o cf or a macro. but this is what i would like the outcome to be from either cf or a macro. if I understand everything correctly a macro would be best. any help would be appreciated

yep forgot the $,well i was leaving work and by the time i remembered was on train home!

ok yea that did work thanks. one more question. it highlight everything 5 days before today. i tried modifying it as another formula for 5 from now it did work. how would I do it as a second condition for everything 5 days from today with same conditions???

Also since this work book gets update monday - saturday when a new entry is entered will it automatically be included in the conditional formatting or would that have to be done everyday?

Originally Posted by ashmcclure

ok yea that did work thanks. one more question. it highlight everything 5 days before today. i tried modifying it as another formula for 5 from now it did work. how would I do it as a second condition for everything 5 days from today with same conditions???

I'm sorry I messed up on this one when I tried to modify for it to be 5 days from now it DIDN'T work. So if someone could help me with another condition so that the dates 5 days from today and column I is empty I can highlight it. Thank You in advance

=AND(ISBLANK($I2),$H2-TODAY()<=5,$H2-TODAY()>=0)
will give it if its within 5 days in the future
as to new entries as long as you put the formating down as far as you feel you need new entries will be ok.
i wouldnt recoment trying to do the whole sheet tho.
inserted rows will need re-doing

Originally Posted by martindwilson

=AND(ISBLANK($I2),$H2-TODAY()<=5,$H2-TODAY()>=0)
will give it if its within 5 days in the future
as to new entries as long as you put the formating down as far as you feel you need new entries will be ok.
i wouldnt recoment trying to do the whole sheet tho.
inserted rows will need re-doing

what would you suggest for the whole sheet?

depends on version of excel. conditional formating 65000+rows times number of columns will use too much memory.
so say you make 20 entry's a day and want a years worth just do from row
1 to about 8000 , dont format columms you're not using. say you're using a to z then only do that width, no point changing colours in cells that you are not looking at

THANKS, yea I'd say about 5000 entries a year. so I will put that into place the cf worked out great thank you very much