ExcelBanter - Comparing date values

Nick,

Thanks for the help.

Ken

"NickH" wrote:

Hi Ken

It looks like your test is the wrong way around. Try If strEndDate <
strStartDate then ..

A simpler way to achieve the same result is:

Private Sub sValidateDate(Target As Range, Optional EndDate As Boolean)
If Not IsDate(Target) Then Exit Sub
If EndDate Then
If (Target.Offset(-1, 0) - Target) = 90 Then Exit Sub
MsgBox "Date must be at least 90 days greater than start date.",
vbOKOnly + vbExclamation, "Invalid Date Entry"
Else
If Target = Date Then Exit Sub
MsgBox "Date must be greater than current date.", vbOKOnly +
vbExclamation, "Invalid Date Entry"
End If
Target = ""
Target.Activate
End Sub

--
Nick

"Ken Warthen" wrote:

I'm using the following code to compare to date values. The values are from
two cells (start date, end date) on a spreadsheet. I need to insure the end
date is 90 days greater than the start date. The cells are formatted as
dates. A subroutine (sValidateDate) is called from code triggered in the
Worksheet_Change event.

When I step through the code with an end date less than 90 days greater than
the start date, the code within the IF strEndDate strStartDate statement
does not trigger. Any idea on what I'm doing wrong here?

Ken Warthen

Private Sub sValidateDate(Target As Range, Optional EndDate As Boolean)
Dim strStartDate
Dim strEndDate

If Target < "" Then
Select Case EndDate
Case False 'Target is a start date
If CVDate(Target.Value) < Date Then
MsgBox "Date must be greater than current date.",
vbOKOnly + vbExclamation, "Invalid Date Entry"
Cells(Target.Row, Target.Column) = ""
Cells(Target.Row, Target.Column).Activate
End If
Case True 'Target is an end date
strStartDate = Cells(Target.Row - 1, Target.Column).Value
strEndDate = Target.Value
strStartDate = strStartDate + 90
If strEndDate strStartDate Then
MsgBox "Date must be at least 90 days greater than start
date.", vbOKOnly + vbExclamation, "Invalid Date Entry"
Cells(Target.Row, Target.Column) = ""
Cells(Target.Row, Target.Column).Activate
End If
Case Else
End Select
End If

End Sub