Trigger event between dates

[Mikeymike_W]

Hi,

I'm wanting to trigger an event between certain dates.
I have a named range called "Licence" on the sheet named "Lists". Within "licence is a date. For the full week before this date is met I want a message box to open, after the date I want the code to be bypassed.

I was trying to use the" Now" function but it doesn't seem to work for a range.

I was trying:
If Range("Licence") <= Now + 7 then
Msgbox "Your licence is about to expire"
End if

Thanks in advance for any support you can provide,

Mike

 

[Osvaldo Palmeiro]

Hi. Try this:

If Range("Licence") >= Date - 7 And Range("Licence") < Date Then MsgBox "Your licence is about to expire"

 

[Peter_SSs]

Within "licence is a date.

That indicates to me that "licence" is a range with more than one cell. Is that correct?
If so, what is in the other cells in "licence"?

When is the date going to be checked? Perhaps when you open the workbook?

 

[Mikeymike_W]

Hi. Try this:

If Range("Licence") >= Date - 7 And Range("Licence") < Date Then MsgBox "Your licence is about to expire"

Thanks Osvaldo, i'll give this a go just now.

 

[Mikeymike_W]

That indicates to me that "licence" is a range with more than one cell. Is that correct?
If so, what is in the other cells in "licence"?

When is the date going to be checked? Perhaps when you open the workbook?

Hi Peter, no it's just one cell that I named Licence.

Yes, I am using the Workbook open to check each time it is opened up.

 

[Mikeymike_W]

Hi. Try this:

If Range("Licence") >= Date - 7 And Range("Licence") < Date Then MsgBox "Your licence is about to expire"

Hi Osvaldo,

I tried your code, it would work when the date had past the licence date but not in the date range specified.
I realised though that it was just a wee tweak in the order that was needed so changed it to the following and it works a charm.
Thanks so much for all your help,

Mike

If Date >= Range("Licence") - 7 And Date < Range("Licence") Then 
MsgBox "Your licence is about to expire"