Dec 17 2018 12:27 PM
My question now is what error is there in my code that it will not work with 2 different actions all in the same sub? They both work independently when I test them out, but I cannot figure out how to get the entire macro to work simultaneously in the same sub. Does anyone know how to compile them properly?
Here is the code that I have put into excel:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim StatusTableRange As Range
Dim StatusMyDateTimeRange As Range
Dim StatusMyUpdatedRange As Range
'Your data Status table range
Set StatusTableRange = Range("N23:O500000")
'Check if the changed cell is in the data tabe or not.
If Intersect(Target, StatusTableRange) Is Nothing Then Exit Sub
'Stop events from running
Application.EnableEvents = False
'Column for the Status date/time
Set StatusMyDateTimeRange = Range("P" & Target.Row)
'Column for last Status updated date/time
Set StatusMyUpdatedRange = Range("Q" & Target.Row)
'Determine if the input date/time should change
If StatusMyDateTimeRange.Value = "" Then
StatusMyDateTimeRange.Value = Now
End If
'Update the updated date/time value
StatusMyUpdatedRange.Value = Now
'Turn events back on
Application.EnableEvents = True
'Timestamp Data
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
'Your data table range
Set myTableRange = Range("E23:L500000")
'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
'Stop events from running
Application.EnableEvents = False
'Column for the date/time
Set myDateTimeRange = Range("B" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("R" & Target.Row)
'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then
myDateTimeRange.Value = Now
End If
'Update the updated date/time value
myUpdatedRange.Value = Now
'Turn events back on
Application.EnableEvents = True
End Sub
Dec 17 2018 12:41 PM - edited Dec 17 2018 12:49 PM
Is it not he first occurrence of this line that's causing you issues?
If Intersect(Target, StatusTableRange) Is Nothing Then Exit Sub
Could do:
If Intersect(Target, StatusTableRange) Is Nothing Then GoTo LabelName
And then add LabelName: before the 2nd half of your macro runs.