Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Regular
    Joined
    Nov 2011
    Posts
    23
    Location

    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
    VBAX Regular
    Joined
    Nov 2011
    Posts
    23
    Location
    I figured it out. Thanks anyway

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    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 [Code].... [/Code] 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
    VBAX Regular
    Joined
    Nov 2011
    Posts
    23
    Location
    A friend helped...and I ended up using a button to activate the code

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

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Everyone: 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
    4,999
    Location
    (Sigh).... its a very small world these days.
    Remember To Do the Following....
    Use [Code].... [/Code] 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
  •