How to retain previous date function
Category: [Excel] Demo Available
How to retain previous date function
Requirement:
The user has a Column M with data validation. If the user selects "In Progress" in Column M, it should update today() function in Column N and If select "Complete" in Column M It should update today() function in Column O but this should not effect Column N(it should retain previous date).
Solution:
Use this macro in the sheet that you want it to run
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldEvents As Boolean
OldEvents = Application.EnableEvents
Application.EnableEvents = False
If Range(ActiveCell.Address).Column = 13 _
And ActiveCell = "In Progress" _
And IsEmpty(Range("N" & ActiveCell.Row)) Then
Range("N" & ActiveCell.Row) = Date
ElseIf Range(ActiveCell.Address).Column = 13 _
And ActiveCell = "Complete" _
And IsEmpty(Range("O" & ActiveCell.Row)) Then
Range("O" & ActiveCell.Row) = Date
Else
End If
Application.EnableEvents = OldEvents
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by chirayuw.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use the =TODAY() for start up but freeze that date |
| How to remove slashes from date |
| How to calculate duration difference between two dates |
| How to convert US to UK dates |
| How to find the maximum date within date range |
| How to sum from two tables based on From-To date criteria |
| How to drag every Nth column, with varying starting column by date |
| How to use a VBA code to change cells colours based on date in other cells |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.