SOLVED

2 columns vba timestamp

Contributor

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.

5 Replies
best response confirmed by Robert1290 (Contributor)
Solution

@Robert1290 

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

 

Many thanks Hans, that works like a dream, and you have written it in a way that I can change to include more columns if required.

Thanks again
@Hans Vogelaar

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

@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
@Hans Vogelaar

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.