Auto-change dropdown value when Due Date passes

Hi,

I have the attached worksheet, in which we keep track of meeting topics in each row. Each row also contains a status & Due Date. When the status is "Open", but the due date is passed, I would like this to auto change to "Due".

Right now I am using a change event, which obviously does not really help since I have to activate the cell in some way to trigger the code. Does anyone have any idea what code I would need to make this work?

Details:
Column I contains a drop-down with 4 statuses (Due, Open, Done, Closed). This is the text I want changed when the due date passes.
Column J contains the due date
Column M contains a formula which when the status is either Due or Open & the due date is passed, it is TRUE.

Current code which does not solve my issue:

Please Login or Register  to view this content.

ExcelForum_Status Change Based on Date.xlsm

Hi pareshj,

I'm not too savvy altering code to fit the location. How exactly would you change it so that it works in ThisWorkbook? Also, do you mean that the code should be in both locations or should I move it?

Thank you for your input.


FYI this is what I tried:

Please Login or Register  to view this content.

Hi,

Thanks for including the attachment - it makes it much easier to understand.

I just had to changed the >= to <= and then it acheived the primary function. However, this also is changing the ones that say "Done" and "Closed" which I would like to keep the same even though the dates are passed. Do you know how to include this parameter into the code?

Works like a charm! All I did was once again change the code from >= to <=.

Thanks again!