Thanks Bob,

i got that, Thank you verymuch.
and one more issue, hw do i select the entire column,here its only for
A1,when i tried like A1:A100,this giving some error 13, type mismatch.

"Bob Phillips" wrote:

I used a worksheet name of check, you will need to change it to the actual
name.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rajesh" wrote in message
...
Hi Philips,

Thank you verymuch.
but i'm getting an error like-Runtime error 9,Subscript out of range.



"Bob Phillips" wrote:

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.