2 columns vba timestamp

[Robert1290]

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.

[#2 · Accepted 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

[#3]

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

[#4]

@Hans Vogelaar

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

[#5]

@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

[#6]

@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.

[#7]

@Hans Vogelaar 

What if I wanted timestamp for column "f" by 3 and "h" to be offset by 1?

[#8]

@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?

[#9]

@Hans Vogelaar 

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

[#10]

@AlexjjKim 

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

[#11]

My bad sir
Awesome thanks! I will try it

[#12]

On very last thing
How do I clear timestamp if value in column B and H have been deleted

[#13]

@AlexjjKim 

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

[#14]

You are the greatest!!