Solved: Alert - When 3 conditions are met on the same row

[Ike]

Hello All,

I'm looking for a "Private Sub Worksheet" vba for the following situation.
Excel 2007
On tab (Sheet4) labeled "Orders"
Event trigger = After multiple rows of data have been entered in Column H (data is Date FedEx Delivered a package)
Check to see if the following three conditions are true

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...

1. Message box: A Replacement Only Order has not delivered yet
2. Have the cell in column C have this attribute > ActiveCell.Interior.ColorIndex = 6

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

There could be multiple instances so, it would be cool if the message box could report how many instances occurred.
It wouldn't be necessary for multiple Message box pop ups for each instance. (Just one alert)

Thanks so much in advance.

[Ike]

I figured it out. Thanks anyway

[Aussiebear]

Quote:   Originally Posted by: Ike I figured it out. Thanks anyway

Any chance you can put your solution up for others to learn from?

[Ike]

A friend helped...and I ended up using a button to activate the code

Sub Replacement_Did_Not_Ship() 
    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 - Not Delivered - 10 Day or More - Alert.") 
End Sub 

VBA tags courtesy of www.thecodenet.com

[p45cal]

Quote:   Originally Posted by: Ike A friend helped...and I ended up using a button to activate the code

..or rather you cross-posted at:
http://www.excelforum.com/excel-prog...-same-row.html

[Aussiebear]

(Sigh).... its a very small world these days.