PDA

View Full Version : Solved: Compare dates



blackie42
09-03-2009, 04:23 AM
Hi Guys.

Is there a way (either in a excel formula) or VBA(WS change?) to compare the value of the date in 2 different cells e.g. A1 & A2 (after date in A2 is input) and put a message out that the date in A2 is not in advance of the date in A1 (and then clear the date in A2)

thanks

Jon

mdmackillop
09-03-2009, 04:34 AM
Have a look at Data Validation. You can check the value and set a warning message.

blackie42
09-03-2009, 05:03 AM
Thanks for reply,

Data Validation does put out message but also allows invalid entry to remain i.e. doesn't blank the cell.

Any way to do in worksheet change event?

thanks

Jon

Benzadeus
09-03-2009, 09:01 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then Exit Sub

If Target < Range("A1") Then
MsgBox "Date in A2 is lower than date in A1!", vbCritical, "Error!"
Application.EnableEvents = False
Target = vbNullString
Application.EnableEvents = True
End If
End Sub

blackie42
09-04-2009, 12:55 AM
Ben,

thanks for trying - doesn't work for me

regards

mdmackillop
09-04-2009, 01:12 AM
doesn't work for me
This does not help progress towards a solution.

blackie42
09-04-2009, 01:35 AM
Sorry,

The code appears to do nothing.

If I put a date in A1 and then a date that precedes that date in A2 it accepts with no error message.

It would be nice to have a solution.

thanks
Jon

mdmackillop
09-04-2009, 02:29 AM
Can you post a workbook example so we can see date formats etc.

blackie42
09-04-2009, 03:02 AM
Have attached the example as requested

thanks

Benzadeus
09-04-2009, 03:32 AM
My mistake. Change If Not Intersect(Target, Range("A2")) Is Nothing Then Exit Sub

for
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub

blackie42
09-04-2009, 06:38 AM
Thanks Ben,

works fine now

regards
Jon