Using VBA to Record a Date Stamp

[Edd_de Spretter]

Hello,

Any help would be greatly received, I am currently using the below VBA to automatically record a date stamp when amending text in cells A1 to A10.

Does anyone know how I would adapt the code to function for data in more than one cell, for example to record a date stamp when I alter text in A1 to A10 and B1 to B10.

This is the code I am currently using:

Thanks in advance.

 

[Skyybot]

Try this:

Set MyDataRng = Union(Range("A2:A10"),Range("B2:B10"))

 

[JoeMo]

I'm confused about exactly what range you want to include in the range that will trigger a date stamp when manual changes are made to any of its cells. The code below will trigger a date stamp if any cell or cells are changed in A1:A10. This will place a date stamp in any empty companion cell or cells in column B.

If you can elaborate on where you want date stamps to appear if the trigger range is changed to A1:B10, the code can be modified accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, myDataRange As Range
Set myDataRange = Range("A1:A10")
If Not Intersect(myDataRange, Target) Is Nothing Then
    For Each c In Intersect(myDataRange, Target)
        If c.Offset(0, 1) = "" Then c.Offset(0, 1).Value = Now()
    Next c
End If
End Sub

 

[Edd_de Spretter]

I'm confused about exactly what range you want to include in the range that will trigger a date stamp when manual changes are made to any of its cells. The code below will trigger a date stamp if any cell or cells are changed in A1:A10. This will place a date stamp in any empty companion cell or cells in column B.

If you can elaborate on where you want date stamps to appear if the trigger range is changed to A1:B10, the code can be modified accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, myDataRange As Range
Set myDataRange = Range("A1:A10")
If Not Intersect(myDataRange, Target) Is Nothing Then
    For Each c In Intersect(myDataRange, Target)
        If c.Offset(0, 1) = "" Then c.Offset(0, 1).Value = Now()
    Next c
End If
End Sub

Hi Joe,

Thank you for your reply.

Essentially in my spreadsheet if initials are added to cells in Column A I want the time stamp to record in the cells of column C.

If subsequently cells in column B are updated I want the time stamp to record in the cells of column D.

Thank you,

 

[JoeMo]

Hi Joe,

Thank you for your reply.

Essentially in my spreadsheet if initials are added to cells in Column A I want the time stamp to record in the cells of column C.

If subsequently cells in column B are updated I want the time stamp to record in the cells of column D.

Thank you,

I've assumed if the date stamp cell is not empty you don't want to overwrite it. If that's not what you want, just remove this bit "If c.Offset(0, 2) = "" Then" from the line just above the Next c line.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, myDataRange As Range
Set myDataRange = Range("A1:B10")
If Not Intersect(myDataRange, Target) Is Nothing Then
    For Each c In Intersect(myDataRange, Target)
        If c.Offset(0, 2) = "" Then c.Offset(0, 2).Value = Now()
    Next c
End If
End Sub

 

[Edd_de Spretter]

Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, myDataRange As Range Set myDataRange = Range("A1:B10") If Not Intersect(myDataRange, Target) Is Nothing Then For Each c In Intersect(myDataRange, Target) If c.Offset(0, 2) = "" Then c.Offset(0, 2).Value = Now() Next c End If End Sub

That works perfectly, thank you very much for your assistance.