I want to have an excel spreadsheet that will automatically enter the date when information is selected from a drop down menu. But to do this multiple times in a year. If I create a formula it updates the the date every time it's opened. So I'm learning that a VBA code needs to be used instead so the date stays when the data is first entered. I don't want it to change every time the spreadsheet is opened.
I have found one code that will work, but I need this to be done on multiple columns if there is data selected from the drop down menu. For example, throughout the year, people would continually update the data and I want the date to show automatically when new data is entered into the next column. Another question too is will this transfer to Google Sheets?
Here's the code I found that seems to work on the first set of columns, E and F
Sub Worksheet_Change(ByVal Target As Range)
'
Dim RowThatChanged As Long
'
If Not Intersect(Target, Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)) Is Nothing Then
RowThatChanged = Target.Row
Range("F" & RowThatChanged).Value = Format(Now, "mm-dd-yyyy")
End If
End Sub
But I need it to do the same thing to these columns when the data is updated, (G & H), (I & J), (K & L), (M & N), for example.
CodePudding user response:
This will work for excel.
Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rng As Range
If Target.CountLarge > 1000 Then Exit Sub 'some reasonable limit on how many cells to handle...
'in a worksheet code module `Me` is the worksheet itself
Set rng = Application.Intersect(Target, Me.Range("E:E,G:G,I:I,K:K,M:M")) 'for example
If Not rng Is Nothing Then
For Each c In rng.Cells
c.Offset(0, 1).Value = Format(Now, "mm-dd-yyyy") 'add date one column over
Next c
End If
End Sub
However if your real target is Google Sheets you should ask a question specifically about that.