Forum Discussion

Brass Contributor

Jan 20, 2022

Solved

2 columns vba timestamp

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

Right-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

Robert1290

Brass Contributor

Jan 21, 2022

HansVogelaar

Is it possible to add in this code so that it only starts from Row 6 please?

HansVogelaar

MVP

Jan 21, 2022

Robert1290

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

AlexjjKim
Copper Contributor
Nov 13, 2022
HansVogelaar
What if I wanted timestamp for column "f" by 3 and "h" to be offset by 1?
- HansVogelaar
  MVP
  Nov 13, 2022
  AlexjjKim
  
  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?
  - AlexjjKim
    Copper Contributor
    Nov 13, 2022
    HansVogelaar
    
    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
Robert1290
Brass Contributor
Jan 21, 2022
HansVogelaar

Thanks, that works a treat, and I have 2 working code for future, which I can amend. Your code is great, I couldn't do this with other code I received. Many thanks again.