macro for excel msg pop up when certain conditions are met

[Jenny]

Hi,
I am trying to come up with a macro that will display a pop up box
when certain criteria are met. The user will enter a date in cell D2
and a zip code in cell D3 that will then automatically display a date
in cell G3 via vlookup. So once these two cells are populated by the
user I need this macro to run and do the following: If the date in
cell G3 is after the date in cell D2, I want a pop up message box to
say that the date is in the future. I've tried formulas and
conditional formatting to warn users, but they ignore these, so I need
a pop up that they would have to click OK on to exit out of to
hopefully get the message across. Thank you for any help.

 

[Luke M]

Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
'Only run if change made to D3, and D2 and D3 are not = ""
If Intersect(Target, Range("D3")) Is Nothing Or _
Range("D2").Value = "" Or Range("D3").Value = "" Then Exit Sub

If Range("G3").Value > Range("D2").Value Then
MsgBox "You are in the future!", vbOKOnly, "WARNING!"
End If

End Sub

 

[Otto Moehrbach]

Jenny
This macro will fire when the contents of D3 is changed. It will then
look at G3 and D2. If G3 is greater than D2, a message box will pop up
telling the user "The date is in the future." and a noise will sound to
attract his attention. The box will stay there until he clicks on the OK
button. Note that this macro is a Worksheet event macro and must be placed
in the sheet module of the pertinent sheet. You access that module by
right-clicking on the sheet tab and selecting View Code. Paste this macro
into that module. "X" out of the module to return to your sheet. HTH
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("D3")) Is Nothing Then
If Range("G3") > Range("D2") Then
MsgBox "The date is in the future.", 16, "READ THIS"
End If
End If
End Sub

 

[Jenny]

This is perfect! It works as I want.

Just one question, if I wanted it to run every time cell D2 or D3 are
modified, how do I do that?

 

[Jenny]

Both macros work great. Thank you so much!