Multiple Worksheet_Change events issue

[hgfantomas]

So, I have two rows where I enter a date (row 9 and row 12 - for columns B thru L). I have a VBA macro that compares it to the date in corresponding column in row 5. If row 5 is older than input date, then the message box pops up. I am able to do this for row 9 but cannot seem to add the trigger for row 12. Any help? See below for my code so far.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r2 As Range, s2 As String

If Intersect(Target, Range("B5:L9"), Range("B5:L12")) Is Nothing Then Exit Sub
For Each r2 In Target.Rows
    If Intersect(Range("B5:L5"), r2.EntireColumn).Value < Intersect(Range("B9:L9"), r2.EntireColumn).Value And Intersect(Range("B9:L9"), r2.EntireColumn) <> vbNullString Then s2 = s2 & ", " & r2.Address
Next r2
If s2 <> vbNullString Then MsgBox "Entry Date Past Due; Comment Required", vbOKOnly + vbExclamation

End Sub

 

[myall_blues]

Try changing this line:

If Intersect(Target, Range("B5:L9"), Range("B5:L12")) Is Nothing Then Exit Sub

to

If Intersect(Target, Range("B5:L9, B5:L12")) Is Nothing Then Exit Sub

and change the rest of your code accordingly.

BUT B5:L9 is a subset of B9:L12 so the B5:L9 is redundant. Since you're only trying to catch changes in rows 9 and 12 then I think what you actually need is

If  Intersect(Target, Range("B9:L9, B12:L12")) Is Nothing Then Exit Sub

 

[hgfantomas]

Try changing this line:

If Intersect(Target, Range("B5:L9"), Range("B5:L12")) Is Nothing Then Exit Sub

to

If Intersect(Target, Range("B5:L9, B5:L12")) Is Nothing Then Exit Sub

and change the rest of your code accordingly.

BUT B5:L9 is a subset of B9:L12 so the B5:L9 is redundant. Since you're only trying to catch changes in rows 9 and 12 then I think what you actually need is

If  Intersect(Target, Range("B9:L9, B12:L12")) Is Nothing Then Exit Sub

That worked. thank you