Timestamp cell change

[Jayzon]

Hi
I have a range of cells C2-G2 and if X is entered into any of these cells i would like O2 to timestamp the event.

And timestamp updated if X is entered into another of these cells.

Is this possible with a formula ?

Thank you in advance.
Jayzon

 

[Joe4]

Hi
I have a range of cells C2-G2 and if X is entered into any of these cells i would like O2 to timestamp the event.

And timestamp updated if X is entered into another of these cells.

Is this possible with a formula ?

Thank you in advance.
Jayzon

Not with formula, as with formula would use NOW() which returns current date and time. The problem is, that is not frozen in time and is constantly updating.
You would use automated VBA code do this. Are you open to a VBA solution? We would give you the code, and it would just be "plug-and-play".

 

[Jayzon]

Not with formula, as with formula would use NOW() which returns current date and time. The problem is, that is not frozen in time and is constantly updating.
You would use automated VBA code do this. Are you open to a VBA solution? We would give you the code, and it would just be "plug-and-play".

Yes i am open to a VBA solution, if i can expand as needed to add more rows like C3-G3 and C4-G4 adding a stamp to O3 or O4 and so on.

 

[Joe4]

OK, here is code that would work on row 2-100. You can adjust your ending row number however you like.

In order for this code to run automatically, it must be placed in the proper sheet module. One easy way to ensure this is to go to the sheet you wish to run this against, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up. Then save and close that window.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if a cell is updated in range C2:G100
    If Not Intersect(Target, Range("C2:G100")) Is Nothing Then
'       See if updated value in "X"
        If UCase(Target.Value) = "X" Then
'           Add time stamp to column O
            Cells(Target.Row, "O").Value = Now()
        End If
    End If
   
End Sub

Now, whenever you enter "X" or "x" anywhere in C2:G100, it will automatically add a date/time stamp to column O of that row.

 

[Jayzon]

OK, here is code that would work on row 2-100. You can adjust your ending row number however you like.

In order for this code to run automatically, it must be placed in the proper sheet module. One easy way to ensure this is to go to the sheet you wish to run this against, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up. Then save and close that window.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if a cell is updated in range C2:G100
    If Not Intersect(Target, Range("C2:G100")) Is Nothing Then
'       See if updated value in "X"
        If UCase(Target.Value) = "X" Then
'           Add time stamp to O2
            Cells(Target.Row, "O").Value = Now()
        End If
    End If
   
End Sub

Now, whenever you enter "X" or "x" anywhere in C2:G100, it will automatically add a date/time stamp to column O of that row.

Works like a charm, thank you very much

 

[Joe4]

You are welcome.
Glad I was able to help!