excel 2000

[F.Kourantis]

Hi ,
i need some help in Microsoft Excel 2000
I want to write a date in one cell and if its possible a month before this day come, Excel somehow to give a warning.
I mean this:
in one cell i have this date: 01/12/2002 and i want at 01/11/2002 to take a warning from Excel

Thanks anyone who can help me.

[Steve R Jones]

Do you mean a warning as in you would like excel to open up all by itsself and do a reminder? If so, no can do.

[pammyjleary]

If I am understanding you correctly, you want it to let you know when something will expire? You can make it turn red using "conditional formatting" with a formula...

[smurfy]

How about something like this in a VBA module:

Sub Auto_Open()

Dim AlarmdateMonth As Integer
Dim AlarmdateDay As Integer
Dim Alarmdate As Date

Sheets("Sheet1").Activate
'substitute the actual sheet name in place of "Sheet1" here

Alarmdate = Cells(1, 1)
AlarmdateMonth = Month(Cells(1, 1)) - 1
AlarmdateDay = Day(Cells(1, 1))
' substitue the correct cell reference (row,column) that contains the date in place of 1, 1 in all three variable definitions

If AlarmdateMonth = Month(Now) Then
If AlarmdateDay = Day(Now) Then
MsgBox "Today is one month prior to " & Alarmdate, vbInformation, "Alarm"
End If
End If


End Sub

This is done as Auto_Open() which means it will be checked every time the workbook is opened. There are other "on event" methods that can be used .

[smurfy]

Hi Filip, got your PM and Email, been a busy weekend.

The ocde I posted is Visual Basic for Applications.
Here is a step by step to "install it" in your excel spreadsheet.

Step 1 = Open your workbook.
Step 2 = Tools>Macro>Visual Basic Editor
Step 3 = (From Microsoft Visual Basic window) Insert>Module
Step 4 = Copy and paste everything from "Sub Auto_Open()" to "End sub" in my last post into the module.
Step 5 = Substitute the name of your worksheet tab and cell reference containing your "alarm date" where I have indicated in my notes in green.
Step 6 = Save worksheet and close it.
Step 7 = Re-open it.
You will be prompted to enable macros or disable macros - Enable them.
Code will run and check dates, if no match, sheet will be ready for action.

To test the code, change your "alarm date" data to one month from today, save and close & re-open, you should get the message pop-up.

[F.Kourantis]

Thanks so much for your reply
But i have stacked.
Here is the problem:

Sub Auto_Open()

Dim AlarmdateMonth As Integer
Dim AlarmdateDay As Integer
Dim Alarmdate As Date

Sheets("Sheet1").Activate
'substitute the actual sheet name in place of "Sheet1" here

Alarmdate = Cells(10, E)
AlarmdateMonth = Month(Cells(10, E)) - 1
AlarmdateDay = Day(Cells(10, E))
' substitue the correct cell reference (row,column) that contains the date in place of 1, 1 in all three variable definitions

If AlarmdateMonth = Month(Now) Then
If AlarmdateDay = Day(Now) Then
MsgBox "Today is one month prior to " & Alarmdate, vbInformation, "Alarm"
End If
End If


End Sub

somethings wrong in < Alarmdate = Cells(10,E) > .Excel says to debug and this line is yellow.
Please check
Thanks again
ilip

Alarmdate = Cells(10.E)

[smurfy]

Cell reference must be numeric (Row, Column)
so (1, 1) is Cell A1
Cell E10 would be
(10, 5),
not (10, E).

Good luck.

[F.Kourantis]

You have right. It works.....

Many thanks for this
Filip

[F.Kourantis]

i want also to turn in red the specific sell each time the alarm appears.
Thanks

[rami3]

To do that you would use conditional formating, thats all I can tell you though untill you post your problem in more detail. Conditional formating is'nt hard to figure out anyway, check your excel help files.

RB :)

[smurfy]

rami3, I have merged this with Filip's old thread so everyone knows the background.

Filip, as rami3 suggests, conditional formatting will do this for you.

[F.Kourantis]

Sorry but i have no idea what is <coditional formating>
If you please explain this ill be very greatfull
Thanks anyway
Filip

[mhl]

Click on Conditional Formatting on the Format menu and it's fairly self-evident. For example, if you're using an accounting spreadsheet, you may want negative figures to show in red . Or in a sales report, figures which exceed target in bold .

[smurfy]

Filip, please use the "Post REPLY" button, not the "New Topic" button. (I've just merged this with your old topic again).

Anyway, after mucking around with conditional formatting for 30 minutes I realise that it's probably not going to achieve what you need in conjunction with the VBA code I have given you.

So, add the following (red) lines into your code...

If AlarmdateMonth = Month(Now) Then
If AlarmdateDay = Day(Now) Then
MsgBox "Today is one month prior to " & Alarmdate, vbInformation, "Alarm"
Cells(10, 5).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End If
End If

This will turn your date cell background color RED whenever the condition that raises the alarm message box is met.

[F.Kourantis]

thank you very much for your repply but after this i have this error: < Compile error: End if without block if >.The complete code for your help is:

Sub Auto_Open()

Dim AlarmdateMonth As Integer
Dim AlarmdateDay As Integer
Dim Alarmdate As Date

Sheets("S1").Activate

Alarmdate = Cells(3, 3)
AlarmdateMonth = Month(Cells(3, 3)) - 1
AlarmdateDay = Day(Cells(3, 3))

If AlarmdateMonth = Month(Now) Then
If AlarmdateDay = Day(Now) Then
MsgBox "Today is one month prior to " & Alarmdate, vbInformation, "Alarm"
Cells(3, 3).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic

End If
End If

Thanks again for your time
Filip

[smurfy]

Ahhh whoops!
Missed a line.
:(


Sub Auto_Open()

Dim AlarmdateMonth As Integer
Dim AlarmdateDay As Integer
Dim Alarmdate As Date

Sheets("S1").Activate

Alarmdate = Cells(3, 3)
AlarmdateMonth = Month(Cells(3, 3)) - 1
AlarmdateDay = Day(Cells(3, 3))

If AlarmdateMonth = Month(Now) Then
If AlarmdateDay = Day(Now) Then
MsgBox "Today is one month prior to " & Alarmdate, vbInformation, "Alarm"
Cells(3, 3).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If

End Sub