Jan 20 2022 02:29 PM
Hello All, I would love to have code which inserts a timestamp in column G whenever column F is populated, and a timestamp in column I whenever column H is populated. Your help is greatly appreciated.
Jan 20 2022 02:34 PM
SolutionRight-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("F:F,H:H"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Intersect(Range("F:F,H:H"), Target).Offset(0, 1).Value = Now
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Jan 20 2022 02:47 PM
Jan 21 2022 06:49 AM
Jan 21 2022 06:53 AM
Change the constant TheCells, for example:
Private Sub Worksheet_Change(ByVal Target As Range)
Const TheCells = "F6:F1000,H6:H1000"
If Not Intersect(Range(TheCells), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Intersect(Range(TheCells), Target).Offset(0, 1).Value = Now
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Jan 21 2022 07:48 AM
Nov 13 2022 10:52 AM
What if I wanted timestamp for column "f" by 3 and "h" to be offset by 1?
Nov 13 2022 12:25 PM
The timestamp would end up in column I for both F and H: I is 3 columns to the right of F and 1 column to the right of H. Is that really what you want?
Nov 13 2022 12:42 PM
Sorry, what I'm trying to accomplish is an original creation date(if data entered in column B then enters today in column G
Any update to status in column H will update column "i"
I am fairly new to all this and I have no trading on VB, but I'm trying to fake it til I make it hahaha
Nov 13 2022 12:53 PM
That is different from what you first asked...
Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B:B"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Intersect(Range("B:B"), Target).Offset(0, 5).Value = Now ' or Date
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
If Not Intersect(Range("H:H"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Intersect(Range("H:H"), Target).Offset(0, 1).Value = Now ' or Date
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Nov 13 2022 02:41 PM
Nov 13 2022 02:48 PM
Like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Range("B:B"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(Range("B:B"), Target)
If rng.Value = "" Then
rng.Offset(0, 5).ClearContents
Else
rng.Offset(0, 5).Value = Now ' or Date
End If
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
If Not Intersect(Range("H:H"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(Range("H:H"), Target)
If rng.Value = "" Then
rng.Offset(0, 1).ClearContents
Else
rng.Offset(0, 1).Value = Now ' or Date
End If
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Jan 20 2022 02:34 PM
SolutionRight-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("F:F,H:H"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Intersect(Range("F:F,H:H"), Target).Offset(0, 1).Value = Now
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub