Can't see it - file sharing sites are blocked by the company firewall.
You could do something like this. Paste this code in the worksheet code module in a
copy of your workbook:-
Code:
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Const KEY_COL As Integer = [COLOR=red][B]1
[/B][/COLOR]Const DATE_COL As Integer = [COLOR=red][B]13
[/B][/COLOR]Const NOTIFIED_COL As Integer = [COLOR=red][B]14[/B][/COLOR][/FONT]
[FONT=Courier New]Const ALERT_PERIOD As Integer = 30
[/FONT]
[FONT=Courier New][COLOR=green]' use this event handler as it's called every time someone moves around the worksheet[/COLOR][/FONT]
[FONT=Courier New]Private Sub Worksheet_Calculate()[/FONT]
[FONT=Courier New] Dim iLast As Long
Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet
iLast = ws.Cells(ws.Rows.Count, DATE_COL).End(xlUp).Row
For iRow = 2 To iLast
If Not IsEmpty(ws.Cells(iRow, DATE_COL)) And IsEmpty(ws.Cells(iRow, NOTIFIED_COL)) Then
If ws.Cells(iRow, DATE_COL) < Now() + ALERT_PERIOD Then
[COLOR=green]' if the date cell isn't empty and it's within the next 30 dayts and we haven't already
' notified the user, then display a message box and set the notification date
[/COLOR] MsgBox "Date for " & ws.Cells(iRow, KEY_COL) & " expires on " _
& Format(ws.Cells(iRow, DATE_COL), "dd/mm/yyyy")
ws.Cells(iRow, NOTIFIED_COL) = Format(Now(), "dd/mm/yyyy")
End If
End If
Next iRow
End Sub[/FONT]
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=Courier New] Dim oCell As Range
Dim ws As Worksheet
Set ws = ActiveSheet
If Not Intersect(Target, ws.Columns(DATE_COL)) Is Nothing Then
For Each oCell In Intersect(Target, ws.Columns(DATE_COL))
[COLOR=green]' if the user changes the expiry date, reset the notifcation flag
[/COLOR] oCell.Offset(0, 1).ClearContents
Next oCell
End If
End Sub[/FONT]
Set
DATE_COL to point to the column which holds the expiry date,
NOTIFIED_COL to point to a spare column where we'll store the date when the warning message was displayed and
KEY_COL to point to whichever column holds the identifying information for that entry. I've set them to
M (as you said),
N (you have to set this to a spare column) and
A (which might hold the person's name, for example).
Whenever the worksheet is recalculated
*, the date column is checked and if it's in the next
ALERT_PERIOD days and the notification column is blank, you get a warning and the notification date is set. Whenever the expiry date is changed, the notification flag is reset to blank.
* This assumes that your worksheet has formulae in it which trigger the
Worksheet_Calculate event.
See how you go with that.
