How to combine 2 Private Sub Worksheet_Change(ByVal Target As Range)
These codes don't work when i run them together!!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim G As Range, H As Range, Inte As Range, r As Range
Set G = Range("G:G")
Set Inte = Intersect(G, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Inte
If r.Offset(0, 1).Value = "" Then
r.Offset(0, 1).Value = Date
End If
Next r
Application.EnableEvents = True
Exit Sub
Dim rngDest As Range
Set rngDest = Accepted.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
If UCase(Target) = "ACCEPTED" Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End Sub
Microsoft ExcelMicrosoft Office
Last Comment
Martin Liss
8/22/2022 - Mon
Martin Liss
What are you trying to do with this line?
Set rngDest = Accepted.Range("rngDest")
Nicole Swisher
ASKER
I got the second part of the code to work. The Set rngdest = Accepted.Range correlates with the worksheet "accepted". I did that for each of the worksheets listed in my document. But what i would like to do is have this code (pasted below) work so that when I add a comment in column g that the date I added the comment to automatically appear in column H.
rivate Sub Worksheet_Change(ByVal Target As Range)
Dim G As Range, H As Range, Inte As Range, r As Range
Set G = Range("G:G")
Set Inte = Intersect(G, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Inte
If r.Offset(0, 1).Value = "" Then
r.Offset(0, 1).Value = Date
End If
Next r
Application.EnableEvents = True
Exit Sub
I have uploaded the file for reference. Thanks again for your help!!
Set rngDest = Accepted.Range("rngDest")