PDA

View Full Version : Date entry validation



Grilleman
02-12-2012, 12:01 PM
Hello -

I have two cells formatted to contain date values. If cell A1 contains a date value, cell B1 can only contain a date value prior to the date value entered in cell A1. If cell A1 is empty, cell B1 can contain any date value.

Is there a formula or VBA code that can accommodate my need.

Thanks!!

Grilleman

mikerickson
02-12-2012, 12:54 PM
Try Data Validation with the formula

=(SIGN(A1)*B1<A1)

omp001
02-12-2012, 01:20 PM
paste the code below in the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$A$1" And Target.Address <> "$B$1" Then Exit Sub
If [A1].Value = "" Or [B1].Value = "" Then Exit Sub

If [A1].Value <= [B1].Value Then
Application.EnableEvents = False
Application.Undo
MsgBox "B1 must be prior to A1"
End If
Application.EnableEvents = True
End Sub