VBA timestamps

[Jetx227]

Hey guys,
So recently I used a thread I saw on here to create a code that will input a time and date whenever a cell value changes. I used it for one range (the first part of my code that I will post), but then when I added the second part it worked once and then stopped working all together. I even took out the whole second part and for some reason the first part wouldn't work at all. Any help would be appreciated. Thanks!

Dim WorkRng As Range, CredWorkRng As Range
Dim Rng As Range, CredRng As Range
Dim xOffsetRow As Integer, xOffsetCol As Integer


'First Part
Set WorkRng = Intersect(Application.ActiveSheet.Range("J7"), Target)
xOffsetRow = 3
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(xOffsetRow, 0).Value = Now
            Rng.Offset(xOffsetRow, 0).NumberFormat = "mm-dd-yyyy, hh:mm:ss"
        Else
            Rng.Offset(xOffsetRow, 0).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If


'Second Part
Set CredWorkRng = Intersect(Application.ActiveSheet.Range("U:U"), Target)
xOffsetCol = 1
If Not CredWorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each CredRng In CredWorkRng
        If Not VBA.IsEmpty(CredRng.Value) Then
            CredRng.Offset(0, xOffsetCol).Value = Now
            CredRng.Offset(0, xOffsetCol).NumberFormat = "mm-dd-yyyy"
        Else
            CredRng.Offset(0, xOffsetCol).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If


End Sub

 

[Fluff]

Firstly run this

Sub chk()
Application.EnableEvents = True
End Sub

You're code should be working again.
Secondly, are you simply changing J7 for the first part & a single cell anywhere in col U for the second?

 

[Jetx227]

Ok, just ran it, and yeah, I have a number value in J7 that the I change (so I wanted to keep the time and date of the last time I updated it) and Col U is currently empty but as I update it I wanted to keep track of the date (i was just feeling lazy and didn't want to manually input it). I put a value into U and it updated correctly the first time, but then it threw up an error saying "You cannot do this to a merged cell" or something along those lines. So I got rid of the merged cells and then it stopped working all together.

 

[Fluff]

Best suggestion is NEVER used merged cells. They cause no end of problems & are not worth the hassle.
If you are only changing one cell at a time try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("J7")) Is Nothing Then
      If Target.Value = "" Then
         Target.Offset(3).ClearContents
      Else
         With Target.Offset(3)
            .Value = Now
            .NumberFormat = "mm-dd-yyyy, hh:mm:ss"
         End With
      End If
   ElseIf Not Intersect(Target, Range("U:U")) Is Nothing Then
      If Target.Value = "" Then
         Target.Offset(, 1).ClearContents
      Else
         With Target.Offset(, 1)
            .Value = Date
            .NumberFormat = "mm-dd-yyyy"
         End With
      End If
   End If
End Sub

 

[Jetx227]

This works great! And yeah, I'm just gonna adjust column widths from now on (haha). Thank you!

 

[Fluff]

Glad to help & thanks for the feedback.
You can get a similar effect to merged cells in 1 row with Centre across selection https://www.techonthenet.com/excel/cells/center_across2016.php