Combining Worksheet Changes

[laurens102]

Hello I am trying to combine two worksheet changes that will update separate timestamps based on if their corresponding columns have been changed. The problem I am having is only one timestamp is changed while the other on stays the same.

Private Sub Worksheet_Change1(ByVal Target As Range)
'Update 20140722
Dim WorkRng1 As Range1
Dim Rng1 As Range1
Dim xOffsetColumn As Integer
Set WorkRng1 = Intersect(Application.ActiveSheet.Range1("B:B"), Target)
xOffsetColumn = 3
If Not WorkRng1 Is Nothing Then
Application.EnableEvents = False
For Each Rng1 In WorkRng1
If Not VBA.IsEmpty(Rng1.Value) Then
Rng1.Offset(0, xOffsetColumn).Value = Now
Rng1.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy, hh:mm:ss"
Else
Rng1.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True

End If

Private Sub Worksheet_Change2(ByVal Target As Range)
'Update 20140722
Dim WorkRng2 As Range2
Dim Rng2 As Range2
Dim xOffsetColumn As Integer
Set WorkRng2 = Intersect(Application.ActiveSheet.Range2("G:G"), Target)
xOffsetColumn = 3
If Not WorkRng2 Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng2
If Not VBA.IsEmpty(Rng2.Value) Then
Rng2.Offset(0, xOffsetColumn).Value = Now
Rng2.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy, hh:mm:ss"
Else
Rng2.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True

End If
End Sub

 

[Joe4]

Welcome to the Board!

Note that in order for the code to run automatically, it MUST be named "Worksheet_Change". You cannot add or remove anything from that name or else it will not run automatically.
And, you are only allowed on "Worksheet_Change" procedure per sheet. So you need to combine your two procedures together.

So you could combine them, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng1 As Range
    Dim Rng1 As Range
    Dim xOffsetColumn As Integer
    Dim WorkRng2 As Range
    Dim Rng2 As Range

    xOffsetColumn = 3

'***FIRST BLOCK***
    Set WorkRng1 = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    If Not WorkRng1 Is Nothing Then
        Application.EnableEvents = False
        For Each Rng1 In WorkRng1
            If Not VBA.IsEmpty(Rng1.Value) Then
                Rng1.Offset(0, xOffsetColumn).Value = Now
                Rng1.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy, hh:mm:ss"
            Else
                Rng1.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If

'***SECOND BLOCK***
    Set WorkRng2 = Intersect(Application.ActiveSheet.Range("G:G"), Target)
    If Not WorkRng2 Is Nothing Then
        Application.EnableEvents = False
        For Each Rng2 In WorkRng2
            If Not VBA.IsEmpty(Rng2.Value) Then
                Rng2.Offset(0, xOffsetColumn).Value = Now
                Rng2.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy, hh:mm:ss"
            Else
                Rng2.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If

End Sub

Note: I did not analyze your code to see if it would do what you wanted, but I did clean up some errors you had in there in reference to your Range variables.

 

[laurens102]

Welcome to the Board!

Note that in order for the code to run automatically, it MUST be named "Worksheet_Change". You cannot add or remove anything from that name or else it will not run automatically.
And, you are only allowed on "Worksheet_Change" procedure per sheet. So you need to combine your two procedures together.

So you could combine them, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng1 As Range
    Dim Rng1 As Range
    Dim xOffsetColumn As Integer
    Dim WorkRng2 As Range
    Dim Rng2 As Range

    xOffsetColumn = 3

'***FIRST BLOCK***
    Set WorkRng1 = Intersect(Application.ActiveSheet.Range("B:B"), Target)
    If Not WorkRng1 Is Nothing Then
        Application.EnableEvents = False
        For Each Rng1 In WorkRng1
            If Not VBA.IsEmpty(Rng1.Value) Then
                Rng1.Offset(0, xOffsetColumn).Value = Now
                Rng1.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy, hh:mm:ss"
            Else
                Rng1.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If

'***SECOND BLOCK***
    Set WorkRng2 = Intersect(Application.ActiveSheet.Range("G:G"), Target)
    If Not WorkRng2 Is Nothing Then
        Application.EnableEvents = False
        For Each Rng2 In WorkRng2
            If Not VBA.IsEmpty(Rng2.Value) Then
                Rng2.Offset(0, xOffsetColumn).Value = Now
                Rng2.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy, hh:mm:ss"
            Else
                Rng2.Offset(0, xOffsetColumn).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If

End Sub

Note: I did not analyze your code to see if it would do what you wanted, but I did clean up some errors you had in there in reference to your Range variables.

You are wonderful. Thank you so much for your help.

 

[Joe4]

You are welcome.