Two timestamps in one sheet

[Dscalf1]

Hello All,

I am trying to get two timestamps in one sheet. I have one range of cells (R6:AF6) and when someone types in any information in any of this range of cells I want a time stamp to show up in cell BH6. Now exactly the same I have this range of cells (AJ6:AX6) and when anyone types on any of these cells I want a timestamp to show up in cell BJ6.

I have this code and it works great for the first part if any cells are entered it shows a timestamp in cell BH 6, however I tried to copy and paste this code to do the second set and it wont work.... Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 18 Or Target.Column > 35 Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, 61) = Now
    Application.EnableEvents = True
End Sub

 

[Joe4]

Use this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   First one
    If Not Intersect(Range("R6:AF6"), Target) Is Nothing Then
        Range("BH6") = Now
    End If
        
'   Second one
    If Not Intersect(Range("AJ6:AX6"), Target) Is Nothing Then
        Range("BJ6") = Now
    End If

End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("R6:AF1000")) Is Nothing Then
      Application.EnableEvents = False
      Cells(Target.Row, 61) = Now
      Application.EnableEvents = True
   ElseIf Not Intersect(Target, Range("AJ6:AX1000")) Is Nothing Then
      Application.EnableEvents = False
      Cells(Target.Row, 62) = Now
      Application.EnableEvents = True
   End If
End Sub

This puts the first time stamp in col BI as per you code

 

[Dscalf1]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("R6:AF1000")) Is Nothing Then
      Application.EnableEvents = False
      Cells(Target.Row, 61) = Now
      Application.EnableEvents = True
   ElseIf Not Intersect(Target, Range("AJ6:AX1000")) Is Nothing Then
      Application.EnableEvents = False
      Cells(Target.Row, 62) = Now
      Application.EnableEvents = True
   End If
End Sub

This puts the first time stamp in col BI as per you code

This worked great thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Dscalf1]

You're welcome & thanks for the feedback

So unfortunately this does not seem to be working when i password protect the sheet... Any ideas?

 

[Joe4]

That is expected, if you are using protection.

You will need to add a line at the beginning of your code to unprotect the sheet, and then re-protect it at the end.
See: https://analysistabs.com/excel-vba/protect-unprotect-worksheets/

 

[Dscalf1]

That is expected, if you are using protection.

You will need to add a line at the beginning of your code to unprotect the sheet, and then re-protect it at the end.
See: https://analysistabs.com/excel-vba/protect-unprotect-worksheets/

I will add this however when i unlocked the sheet now its not working at all i didnt change anything else and im not sure what is happening..

 

[Fluff]

Run this

Sub chk()
   Application.EnableEvents=True
End Sub

and try the code again after unprotecting the sheet

 

[Dscalf1]

Its not working at all now heres my code: Is something wrong?

Private Sub Worksheet_Change(ByVal Target As Range)


ActiveSheet.Unprotect "Bakery"


   If Not Intersect(Target, Range("J6:X75")) Is Nothing Then
      Application.EnableEvents = False
      Cells(Target.Row, 52) = Now
      Application.EnableEvents = True
   ElseIf Not Intersect(Target, Range("AB6:AP75")) Is Nothing Then
      Application.EnableEvents = False
      Cells(Target.Row, 54) = Now
      Application.EnableEvents = True
   End If
   
 ActiveSheet.Protect "Bakery", True, True
   
End Sub