Excel Hints

Results 1 to 6 of 6

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

  1. #1

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

    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.

  2. #2
    I figured it out. Thanks anyway

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,216
    Location
    Quote Originally Posted by Ike
    I figured it out. Thanks anyway
    Any chance you can put your solution up for others to learn from?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

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

    VB:
    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 
    
    
    Formatting tags added by mark007
    Last edited by Aussiebear; 04-19-2012 at 02:45 AM. Reason: Added vba tags to code

  5. #5
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,216
    Location
    (Sigh).... its a very small world these days.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •