Alert - When 3 conditions are met on the same row

Hello All,

I'm looking for vba for the following situation.

Excel 2007

On tab (Sheet4) labeled "Orders"

Event trigger = After data has been entered into the first empty cell(s) of Column H

Check to see if the following three conditions are true (I shall setup a button to activate the VBA code)

1st - Column C - a cell has the phrase -> 2) Replacement Only
2nd - Column B - same row, the cell has a date that is -> older than 10 days
3rd - Column H - same row, the cell is -> blank (should have a date in it - If it has a date - no action regardless of the age of the date in column B)

'When all three conditions are met then a Message box: A Replacement Only Order has not delivered yet.
And have the cell in column C have this attribute... ActiveCell.Interior.ColorIndex = 6

If there re not any instances then no message or alert.

It would be cool if the message box could report how many instances occurred. If not, it wouldn't be

necessary for multiple Message box pop ups for each instance. (Just one alert)


Thanks so much in advance.

i was a bit confused if you wanted this to loop through multiple lines, but this will run on the line you enter a change, i can modify to loop if that is what you are looking for.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 8 Then
    If Range("C" & Target.Row).Value = "2) Replacement Only" And _
        Range("B" & Target.Row).Value < Date - 10 And _
        Range("H" & Target.Row).Value = 0 Then
        
        MsgBox ("A Replacement Only Order has not delivered yet.")
        
        ActiveCell.Interior.ColorIndex = 6
        
    End If
End If
End Sub

what would be the range for the loop?

row 2 through first empty in row in column C?

test this out and let me know how it works

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flaged As Boolean
Dim i As Integer
Flaged = False

For i = 2 To Range("C1048576").End(xlUp).Row
    If Range("C" & i).Value = "2) Replacement Only" And _
        Range("B" & i).Value < Date - 10 And _
        Range("H" & i).Value = 0 Then
        
        ActiveCell.Interior.ColorIndex = 6
    End If
Next

If Flaged = True Then MsgBox ("A Replacement Only Order has not delivered yet.")

End Sub

could you supply a test workbook? and the msgbox was my falut, this shoudl fix that

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flaged As Boolean
Dim i As Integer
Flaged = False

For i = 2 To Range("C1048576").End(xlUp).Row
    If Range("C" & i).Value = "2) Replacement Only" And _
        Range("B" & i).Value < Date - 10 And _
        Range("H" & i).Value = 0 Then
        
        Range("H" & i).Interior.ColorIndex = 6
        Flaged = True
    End If
Next

If Flaged = True Then MsgBox ("A Replacement Only Order has not delivered yet.")

End Sub

this should work, as you are using a button to run it, you dont need it in the change event you can just put it in a new sub

Sub Test
Dim Flaged As Boolean
Dim i As Integer
Flaged = False

For i = 2 To Range("C1048576").End(xlUp).Row
    If Range("C" & i).Value = "2) Replacement Only" And _
        Range("B" & i).Value < Date - 10 And _
        Range("H" & i).Value = 0 Then
        
        Range("H" & i).Interior.ColorIndex = 6
        Flaged = True
    End If
Next

If Flaged = True Then MsgBox ("A Replacement Only Order has not delivered yet.")
End Sub

glad to help, good luck with the rest of the project.