Repeat the same macro twice?

[Lincoln Six Echo]

Hi,

I have the following code to show the last date on which data was updated in column I.

The first section works fine. When I update column I, today's date shows up in column 17 (Q) on that row. However, regardless of what I change, I can't seem to get the second section to work. Basically, I want to add the current date to column 18 (R) for the row that was edited whenever a cell between A and P is updated.

What do I need to change in the 2nd statement to make Excel recognize both?

Thanks for your help.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = 0
If Not Intersect(Target, Range("I:I")) Is Nothing Then
If Target.Rows.Count > 17 Then
If Target.Column = 17 Then Cells(Target.Row, 17).Resize(UBound(Target.Value)).Value = Date
Cells(Target.Row, 17).Resize(UBound(Target.Value)).Value = Date
Else
If Target.Column = 17 Then Cells(Target.Row, 17).Value = Date
Cells(Target.Row, 17).Value = Date
End If
End If
Application.EnableEvents = 1
End Sub


Private Sub Worksheet_Change2(ByVal Target As Range)
Application.EnableEvents = 0
If Not Intersect(Target, Range("A:P")) Is Nothing Then
If Target.Rows.Count > 18 Then
If Target.Column = 18 Then Cells(Target.Row, 18).Resize(UBound(Target.Value)).Value = Date
Cells(Target.Row, 18).Resize(UBound(Target.Value)).Value = Date
Else
If Target.Column = 18 Then Cells(Target.Row, 18).Value = Date
Cells(Target.Row, 18).Value = Date
End If
End If
Application.EnableEvents = 1
End Sub

 

[GTO]

"Private Sub Worksheet_Change2(ByVal Target As Range)"

Hi there,

Nice try, but that won't work. See, the worksheet change and other events, are supplied by Excel. That is to say, they are "pre-written" if you will, events that trap some action, in this case, changing a cell or cells. Thus, Excel simply isn't going to do anything with the second procedure.

You'll want to combine your new code into you existing, melding tests to cover possibilities and what code should be run.

Mark

 

[hellebore08]

Does combining all of your if statements answers the question?

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = 0
If Not Intersect(Target, Range("I:I")) Is Nothing Then
If Target.Rows.Count > 17 Then
If Target.Column = 17 Then Cells(Target.Row, 17).Resize(UBound(Target.Value)).Value = Date
Cells(Target.Row, 17).Resize(UBound(Target.Value)).Value = Date
Else
If Target.Column = 17 Then Cells(Target.Row, 17).Value = Date
Cells(Target.Row, 17).Value = Date
End If
End If

If Not Intersect(Target, Range("A:P")) Is Nothing Then
If Target.Rows.Count > 18 Then
If Target.Column = 18 Then Cells(Target.Row, 18).Resize(UBound(Target.Value)).Value = Date
Cells(Target.Row, 18).Resize(UBound(Target.Value)).Value = Date
Else
If Target.Column = 18 Then Cells(Target.Row, 18).Value = Date
Cells(Target.Row, 18).Value = Date
End If
End If

Application.EnableEvents = 1

End Sub

-e.rgabrieldoronila