Run macro at different intervals based on cell date's distance from today's date

[b.hill]

I would like to be able to look at a date in cell "A2" and if the date is less than 8 days from today then the macro runs once every 12 hours, if the date is between 8 and 14 days from today then the macro runs once every 24 hours, if the date is between 15 and 21 days from today then the macro runs once every 48 hours, and if the date is past 21 days from today then exit the sub.

[Bob Phillips]

So what do you need help with? Working out how to determine the interval, setting up repeated processes, or both?

[b.hill]

I am using the "ontime" function right now to call the macro every 24 hours and I just call the ontime function again at the end of the macro. I feel that I can set up the repeated processes like this (maybe I can't do it this way with multiple time intervals that are dependent on cell values), but I don't have any idea for how to set up the multiple time intervals based on the cell date's difference from today's date.

[CodeNinja]

Something like this might help...

[VBA]
Sub Run1()
Call test1
End Sub

Sub test1()
Dim dt As Date
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim iDaysDiff As Integer

'do code here

dt = Sheet1.Cells(2, 1)
'avoid rounding of dateStamp of now by using dateserial
iDay = Day(Now)
iMonth = Month(Now)
iYear = Year(Now)
iDaysDiff = dt - DateSerial(iYear, iMonth, iDay)

Select Case iDaysDiff
Case 0 To 7
' less than 8 days different, run this macro again in 8 hrs
Application.OnTime Now + TimeValue("08:00:00"), Run1()
Case 8 To 14
'between 8 and 14 days, run this macro again in 24 hours
Application.OnTime Now + TimeValue("24:00:00"), Run1()
Case 15 To 21
'between 15 and 21 days, run this macro again in 48 hours
Application.OnTime Now + TimeValue("48:00:00"), Run1()
End Select



End Sub
[/VBA]

[b.hill]

Thank you. I have been trying to get your code to work, but the macro says "compile error: Expected function or variable" and highlights the "Run1" macro call on the first ontime row of code.

[b.hill]

Got it all figured out! Excel did not like the name "Run1" for a macro so I renamed for something random and instead of , Run1() for the ontime calls I added quotes, took away parentheses, and changed the name. (so it looks like this: , "something_random"