Automatically update user-inputted date after specified time range

PrincessApril · #1 11-30-2019, 08:37 AM

I found a proposed solution on another forum, but the code does not work for me (I don't see any reaction after I open the workbook...perhaps it needs updating or fixing):

Quote:

Code:

Private Sub Worksheet_Activate()
   Dim N As Long, r As Range, rng As Range
   Dim d As Date
   N = Cells(Rows.Count, "G").End(xlUp).Row
   Set rng = Range("G3:G" & N)

   For Each r In rng
      If IsDate(r.Value) Then
         d = r.Value
         If d < Date Then
            r.Value = DateSerial(Year(d) + 1, Month(d), Day(d))
         End If
      End If
   Next r
End Sub

Source: microsoft excel - How do I add 1 year to a specific date once that date has passed? - Super User

Any ideas on what the problem with that code might be? I did the following:

Saved my workbook as xlsm
Hit Alt + 11 and pasted the code
Changed G3 to G6 to match the first cell of data in my worksheet
Saved and Reopened the workbook.
Accepted the "enable content" notification, saved and reopened again, but the old date does not update (I have cell G6 as June 7, 2018, for testing but it does not update).

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Auto update excel graph range, ignore data when date reads 00-Month-00	SavGDK	Excel	2	06-24-2016 08:05 AM
Auto populating dates on timesheet for week from one inputted date	ohi	Word VBA	3	12-04-2014 12:11 PM
Date/Time Formula for Entry level XL user	talon1driver	Excel	2	09-18-2014 02:32 PM
Mail merge with filter using data inputted by user	Tornado70	Mail Merge	6	04-09-2013 07:16 AM
How to update automatically the “file name” as we do for the “Date”?	Jamal NUMAN	Word	2	01-06-2011 02:43 PM