Solved: Compare dates

[blackie42]

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]

Have a look at Data Validation. You can check the value and set a warning message.

[blackie42]

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]

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]

Ben,

thanks for trying - doesn't work for me

regards

[mdmackillop]

doesn't work for me
This does not help progress towards a solution.

[blackie42]

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]

Can you post a workbook example so we can see date formats etc.

[blackie42]

Have attached the example as requested

thanks

[Benzadeus]

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]

Thanks Ben,

works fine now

regards
Jon