Help! I need a macro to check dates and autopopulate cells
 

Hi Mike / JP

Thanks for your help. One problem though:

Whilst your macros work, I've now realised that we also don't want the macro
to change the status to Overdue if the status happens to already be marked as
Complete.

I'm not sure how to adapt your macros to make this improvement? Your help
would be greatly appreciated!

:)

--
If you can measure it, you can improve it!


"Monomeeth" wrote:

Hello

I have a spreadsheet used for managing tasks. Column D is for DUE DATE and
Column F is for STATUS. Presently, Column F has conditional formatting and
validation set so that there are only three possible options for users to
enter: (1) Completed; (2) On Track; and, (3) Overdue.

What I would like is to have a macro which runs automatically on launch to
check the DUE DATE to see if it falls before today's date and, if so, to
change the status to Overdue. I would need the macro to check each row until
it hits a blank row. Of course, if the DUE DATE is today's date or after
today's date, then the Status for that row would be left untouched.

Any help would be most appreciated!

Thanks,

Joe.

--
If you can measure it, you can improve it!

Help! I need a macro to check dates and autopopulate cells
 

Change

If cell.Value < Now Then
cell.Offset(0, 2).Value = "Overdue"
End If

to this:

If cell.Value < Now Then
If cell.Offset(0, 2).Value < "Completed" Then
cell.Offset(0, 2).Value = "Overdue"
End If
End If


HTH,
JP


On May 1, 1:47*am, Monomeeth
wrote:
Hi Mike / JP

Thanks for your help. One problem though:

Whilst your macros work, I've now realised that we also don't want the macro
to change the status to Overdue if the status happens to already be marked as
Complete.

I'm not sure how to adapt your macros to make this improvement? Your help
would be greatly appreciated!

:)

Help! I need a macro to check dates and autopopulate cells
 

Thanks JP. That works perfectly!

--
If you can measure it, you can improve it!


"JP" wrote:

Change

If cell.Value < Now Then
cell.Offset(0, 2).Value = "Overdue"
End If

to this:

If cell.Value < Now Then
If cell.Offset(0, 2).Value < "Completed" Then
cell.Offset(0, 2).Value = "Overdue"
End If
End If


HTH,
JP


On May 1, 1:47 am, Monomeeth
wrote:
Hi Mike / JP

Thanks for your help. One problem though:

Whilst your macros work, I've now realised that we also don't want the macro
to change the status to Overdue if the status happens to already be marked as
Complete.

I'm not sure how to adapt your macros to make this improvement? Your help
would be greatly appreciated!

:)

Help! I need a macro to check dates and autopopulate cells
 

Glad to hear it!

--JP

On May 6, 1:17*am, Monomeeth
wrote:
Thanks JP. That works perfectly!