Insert Timestamp when cell value = 1

[RLJ]

Hello,

I need vba to insert a timestamp into the cell in column G when the cell in column B =1. Conversely, if there is a 1 in the cell in Column B and it is removed, then it removes the timestamp in Column G.

Ex: B8 = 1, then G8 will have the timestamp.

My data is in a table called ToDoList and column B is Done and column G is Timestamp.

Thank you!

 

[Joe4]

How exactly is the value in column B being updated?
Is it being updated manually, or is it a formula?
If a formula, what is the formula?

 

[RLJ]

How exactly is the value in column B being updated?
Is it being updated manually, or is it a formula?
If a formula, what is the formula?

It is being updated manually by user.

 

[Joe4]

Excellent!

Right-click on the sheet name at the bottom of the screen, select “View Code”, and enter the following VBA code in the VB Editor that pops open:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if column B is updated
    If Target.Column = 2 Then
'       Check to see if column B set to 1
        If Target.Value = 1 Then
'           Add date/time stamp to column G
            Target.Offset(0, 5).Value = Now()
        End If
'       Check to see if column B is blank and column G has a date/time stamp
        If Target.Value = "" And Target.Offset(0, 5).Value > 0 Then
'           Remove date/time stamp from column G
            Target.Offset(0, 5).ClearContents
        End If
    End If

End Sub

The code will run automatically as you make manual updates to column B.

 

[RLJ]

Excellent!

Right-click on the sheet name at the bottom of the screen, select “View Code”, and enter the following VBA code in the VB Editor that pops open:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
   
'   Only run if column B is updated
    If Target.Column = 2 Then
'       Check to see if column B set to 1
        If Target.Value = 1 Then
'           Add date/time stamp to column G
            Target.Offset(0, 5).Value = Now()
        End If
'       Check to see if column B is blank and column G has a date/time stamp
        If Target.Value = "" And Target.Offset(0, 5).Value > 0 Then
'           Remove date/time stamp from column G
            Target.Offset(0, 5).ClearContents
        End If
    End If

End Sub

The code will run automatically as you make manual updates to column B.

Most Excellent. It works perfectly. Thanks

 

[Joe4]

You are welcome.