Auto-change dropdown value when Due Date passes

[goomblar]

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

[pareshj]

Hi,

Use the same code in workbook open event. On workbook open, each row with Due date can be checked and respective Status can be updated accordingly.

Regards,
Paresh J

[goomblar]

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.

[pareshj]

Hi,

Check the attached workbook, I have code to trigger on workbook. Please change in this code if any further changes required.

What I have done in the attached workbook, I have checked each date in column J and if condition is not satisfied, Column I will get changed to "Due"

Note: Check the WorkBook Open Event

ExcelForum_Status Change Based on Date.xlsm


Regards,
Paresh J

[goomblar]

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?

[pareshj]

Hi,

Do you want to change the status of only OPEN cases? If yes, You can use the following code for the same:

Note: Only change the following line

Please Login or Register  to view this content.

Regards,
Paresh J

[goomblar]

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

Thanks again!

[pareshj]

Hi,

Please mark this thread as SOLVED if you got your solution and click * Add reputation as a way to thanks.

Regards,
Paresh J