Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("check").Range("A1")
If .Value = Date - 7 And .Value <= Date Then
MsgBox "Contract ends on " & Format(.Value, "dd mmm yyyy")
End If
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rajesh" wrote in message
...
Hi,
I've a Excel sheet with customer name and the contract end date.wht i'm
looking for is i want to get a alert message whn open the workbook 1 week
before the contract end date.