Two time stamp codes working at the same time?

Question

I need help writing a code that will allow for a date/ time stamp in column H when there is any value entered in I. Right now the code below allows for a time-stamp in G, when a value is entered in column B. What do I need to do?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range

    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("B:B"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, 5)
                    .Value = Now
                    .NumberFormat = "mm-dd-yy h:mm AM/PM"

                End With
            Else
                rCell.Offset(0, 5).Clear
            End If
        Next
    End If

ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

I don't quite understand what you need to do. Do you need to store the same timestamp in two different cells? — Barranka, Apr 13 '15 at 23:56
You already have the code that does the job - have you tried playing with it to see if you can get it to repeat these actions for a different range? — SierraOscar, Apr 13 '15 at 23:57
Im trying to get two different timestamps one for taking out the equipment and one for the return of the equipment, — Erica Fran Mateo, Apr 16 '15 at 20:51

score 0 · Answer 1 · 2015-04-14T00:13:54.550

You could either add an ElseIf for a second range or include I:I in the primary check for an intersect and decide where to stuff the timestamp depending on whether it was B:B or I:I that received the addition/deletion/modification. I'll demonstrate the latter.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range

    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("B:B, I:I")) '<- note change
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, 5 + (rCell.Column = 9) * 6) '<- note change
                    .Value = Now
                    .NumberFormat = "mm-dd-yy h:mm AM/PM"

                End With
            Else
                rCell.Offset(0, 5 + (rCell.Column = 9) * 6).Clear '<- note change
            End If
        Next
    End If

ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

I've added I:I to the check for intersect and used VBA's True = (-1) to adjust which column receives the timestamp.

Two time stamp codes working at the same time?

1 Answers1