I am having problems getting a For Each-Next statement to work the way I want
with my 3 conditions. I have an Expense report that on each row I want to
validate that a trip # (column E) is entered if a date is entered (col B) AND
the Sum of Expense rows (M, L & N) is greater than zero. My code (below) is
close but it has one flaw I am having trouble figuring out how to correct. I
want my validations to run on each row independantly and it is not doing this
properly.

For example, if row 20 does not have a trip number but meets the conditions
of not needing one (sum=0), the code will allow me to proceed to row 21. If
row 21 has a trip number, date & Sum is greater than zero, the code will take
me back to row 20 with the missing trip number.

Can anyone help me fix this code to correct this problem? Any help is
greatly appreciated.
======== CODE ======
Public Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("b20:N39")) Is Nothing Then
Dim c As Variant

For Each c In Worksheets("Expense Report").Range("b20:b39").Cells

If c < "" And IsEmpty(Cells(c.Row, "E")) Then

Sum = 0
Sum = Sum + Sheet1.Cells(Target.Row, "M").Value
Sum = Sum + Sheet1.Cells(Target.Row, "L").Value
Sum = Sum + Sheet1.Cells(Target.Row, "N").Value

If Sum 0 Then
Sheet1.Cells(Target.Row, "E").Select
MsgBox "Trip # is required when reporting Travel
Expenses."
Exit For
End If
End If


Next
End If
End Sub

Do you really want to check every row on a selection change? Possibly:

Public Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("b20:N39")) Is Nothing Then
Dim c As Range

set c = cells(Target.row,"B")
If c < "" And IsEmpty(Cells(c.Row, "E")) Then

Sum = 0
Sum = Sum + Sheet1.Cells(Target.Row, "M").Value
Sum = Sum + Sheet1.Cells(Target.Row, "L").Value
Sum = Sum + Sheet1.Cells(Target.Row, "N").Value

If Sum 0 Then
Application.EnableEvents = False
Sheet1.Cells(Target.Row, "E").Select
Application.EnableEvents = True
MsgBox "Trip # is required when reporting Travel
Expenses."
Exit For
End If
End If

End If
End Sub

--
Regards,
Tom Ogilvy

"Kathy - Lovullo" wrote:

I am having problems getting a For Each-Next statement to work the way I want
with my 3 conditions. I have an Expense report that on each row I want to
validate that a trip # (column E) is entered if a date is entered (col B) AND
the Sum of Expense rows (M, L & N) is greater than zero. My code (below) is
close but it has one flaw I am having trouble figuring out how to correct. I
want my validations to run on each row independantly and it is not doing this
properly.

For example, if row 20 does not have a trip number but meets the conditions
of not needing one (sum=0), the code will allow me to proceed to row 21. If
row 21 has a trip number, date & Sum is greater than zero, the code will take
me back to row 20 with the missing trip number.

Can anyone help me fix this code to correct this problem? Any help is
greatly appreciated.
======== CODE ======
Public Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("b20:N39")) Is Nothing Then
Dim c As Variant

For Each c In Worksheets("Expense Report").Range("b20:b39").Cells

If c < "" And IsEmpty(Cells(c.Row, "E")) Then

Sum = 0
Sum = Sum + Sheet1.Cells(Target.Row, "M").Value
Sum = Sum + Sheet1.Cells(Target.Row, "L").Value
Sum = Sum + Sheet1.Cells(Target.Row, "N").Value

If Sum 0 Then
Sheet1.Cells(Target.Row, "E").Select
MsgBox "Trip # is required when reporting Travel
Expenses."
Exit For
End If
End If


Next
End If
End Sub