-
Solved: Compare dates
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
-
Have a look at Data Validation. You can check the value and set a warning message.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
-
[VBA]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[/VBA]
-
Ben,
thanks for trying - doesn't work for me
regards
-
Originally Posted by
blackie42
doesn't work for me
This does not help progress towards a solution.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
-
Can you post a workbook example so we can see date formats etc.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Have attached the example as requested
thanks
-
My mistake. Change [vba] If Not Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
[/vba]
for
[vba] If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
[/vba]
-
Thanks Ben,
works fine now
regards
Jon
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules