Thread: When 3 conditions are met in 3 columns same row - Message Box

Hello All,

I'm looking for vba for the following situation.

Excel 2007

On tab labeled "Orders"
Sheet4 (Orders)

Event trigger = There is a picture object in a cell, has this macro assigned to it, and is clicked on.

Then check to see if the following three conditions are true

1st - Column C - a cell has the phrase "2) Replacement Only" (without quotes)
2nd - Column B - same row cell has a date that is older than today
3rd - Column F - same row has a cell that is blank

When all three conditions are true then a Message box: A Replacement Only Order Did Not Ship
If all conditions are false then a Message box: All Replacement Only Orders Shipped

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)


Thank you in advance.

This code will tell you how many orders did not ship as well as highlight the rows that did not ship.

Sub a()

Range("c1").Select

Dim n As Integer

n = 0

Do While ActiveCell <> ""
    If ActiveCell.Value = "2) Replacement Only" And Cells(ActiveCell.Row, 2) < Date And Cells(ActiveCell.Row, 6) = "" Then
        ActiveCell.EntireRow.Interior.ColorIndex = 6
            n = n + 1
                ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Loop

    If n > 0 Then
        MsgBox n & " Replacement Only Orders Did Not Ship"
    End If
    
    If n = 0 Then
        MsgBox "All Replacement Only Orders Shipped"
    End If
 

End Sub

Alvin, it seems to be working for me. On your test sheet column C reads ")2 Replacement Only" instead of "2) Replacement Only" The macro will only work if column C matches the text exactly. Give it another try and let me know if it works for you. I'm heading off to work soon, but I'll try to sneak a peek back here if I can.