Creating a timestamp from another cell without using If statment

[Parkerpen13]

I currently have a workbook that when data is entered in column A it will return a time stamp in column I with the help of the if statement. This works great however it makes my workbook very slowwwww. Is there another way or a VBA code that could do this same thing that could improve my workbook?

Current formula

=IF(A1<>"",IF(I201<>"",I1,NOW()),"")

 

[Joe4]

This topic has been covered hundreds of times here. Here is one such post: https://www.mrexcel.com/forum/excel-questions/575734-timestamp-want-change-when-workbook-opened.html

Note that using the NOW() function in a formula on your sheet will not work, as it will always return the current date/time (and not "lock in" on the date/time the formulas conditions are met).

 

[Parkerpen13]

Ok so how would I add that code to this code that is already on that sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A3:A1000"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="RCFD"
xRg.Locked = True
Target.Worksheet.Protect Password:="RCFD"
End Sub


and add this code to it
Private Sub Worksheet_Change(ByVal Target As Range)

' Automatically place date/time stamp in cells A1:A1000 when a cell in G1:G1000 is manually updated
If Not Intersect(Target, Range("G1:G1000")) Is Nothing Then
Target.Offset(0, -2) = Now
End If

End Sub

 

[Parkerpen13]

Combining two Private Sub Worksheet_Change VBA Codes help

I would like to combine the two following codes together but have been unsucsesful with making both of them to work. Please help

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A3:A1000"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="RCFD"
    xRg.Locked = True
    Target.Worksheet.Protect Password:="RCFD"
 End Sub

and add this code to it

Private Sub Worksheet_Change(ByVal Target As Range)

 ' Automatically place date/time stamp in cells A1:A1000 when a cell in G1:G1000 is manually updated
    If Not Intersect(Target, Range("G1:G1000")) Is Nothing Then
        Target.Offset(0, -2) = Now
    End If

 End Sub

 

[Fluff]

Re: Combining two Private Sub Worksheet_Change VBA Codes help

How about

Private Sub Worksheet_Change(ByVal Target As Range)
'    On Error Resume Next
    If Not Intersect(Range("A3:A1000"), Target) Is Nothing Then
      Target.Worksheet.Unprotect Password:="RCFD"
      Target.Locked = True
      Target.Worksheet.Protect Password:="RCFD"
   ElseIf Not Intersect(Target, Range("G1:G1000")) Is Nothing Then
      Target.Worksheet.Unprotect Password:="RCFD"
      Target.Offset(0, -2) = Now
      Target.Worksheet.Protect Password:="RCFD"
   End If
 End Sub

Also, when posting code please use code tags, the # icon in the reply window.

 

[Norie]

Re: Combining two Private Sub Worksheet_Change VBA Codes help

You could try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range

    ' Automatically place date/time stamp in cells A1:A1000 when a cell in G1:G1000 is manually updated

    If Not Intersect(Target, Range("G1:G1000")) Is Nothing Then
        Target.Offset(0, -2) = Now
    End If
    
    On Error Resume Next
    Set xRg = Intersect(Range("A3:A1000"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="RCFD"
    xRg.Locked = True
    Target.Worksheet.Protect Password:="RCFD"
    
End Sub

 

[Joe4]

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim xRg As Range
    On Error Resume Next

    Set xRg = Intersect(Range("A3:A1000"), Target)
    If Not xRg Is Nothing Then
        Target.Worksheet.Unprotect Password:="RCFD"
        xRg.Locked = True
        Target.Worksheet.Protect Password:="RCFD"
    End If
    
    If Not Intersect(Target, Range("G1:G1000")) Is Nothing Then
        Target.Offset(0, -2) = Now
    End If
    
End Sub

 

[Fluff]

@Parkerpen13
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.

I have merged both your threads

 

[Joe4]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.

Please have patience and allow time for us to see your response and respond to it.
Remember, this is an all volunteer board, and no one is on-line 24x7!

 

[Parkerpen13]

Re: Combining two Private Sub Worksheet_Change VBA Codes help

This code is locking like it should but it is not returning a time stamp in the G column? Any ideas? I have this code in sheet 1.