Highlight Rows with Conditions met

Hi Riz,

I looked at PMARI's file and can't find a solution. Now I'm curious how you would solve this. Hopefully you can acces "send space" since that is the site I used to upload his file.

Apparently Kisokea is blocking "send space" since my message doesn't stick. That is why I had to break it up.

"http://www.send
space.com/file/r0vc9c"

Best regards,
Trowa

Thanks for the link Trowa. I did look at the file. May be I am misreading his requirement. It seem all what he need is to filter the data on search criteria (MANGO). If he wants he can then copy the visible rows to a new sheet . I must be missing some thing here if it is so obvious.

Hi Riz,

He only wants orders with one specific type of fruit to be moved to another sheet.

So when you look at his first order number (100212) it contains Mango, Apple, Pineapple and Cherry. This order needs to be left alone since it contains multiple types of fruit.
Order 14568 only contains Mango, so this order needs to be moved.
Order 302154 only contains Papaya, but this is not the particular fruit he mentioned in red, so this orde needs to be left alone as well.

When you go by this logic only the four orange lines needs to be moved (to the next sheet or like he did in his file to range G8:K11).

So I was thinking about an inputbox which will let you input the fruitname. Then search through the order numbers for a unique order which matches the inputted fruitname.
But to make things a little more complicated, there can also be an order number with two rows of the same fruit, thus making the order number not unique any more.

Hopefully this clearify's the issue.
BTW it took me quite some time looking at his sheet thinking about what he could mean. For me that is part of the fun in solving query's.

Best regards,
Trowa

Ah I see. Thanks for explaining the issue. A lot will depend on number of rows. One way can be, input box as you suggested to get the items that i want. Once I get it, I sort the data in order# (presuming that it is not sorted or skip it it will be always sorted. Now you start processing row at a time. As you mentioned that I may have order split into rows. The way i would do is.

Sub doGetDetail() 
   Dim sCurrOrder             As String 
   Dim sNextOrder             As String 
   Dim lRow                   As Long 
   Dim sLookFor               As String 
   Dim vLookFor               As Variant 
   Dim dicMyItems             As Object 
   Dim bSkipThisOrder         As Boolean 
   Dim lStartOrder            As Long 
   Dim lPasteAt               As Long 
    
   'WHAT NEEDS TO BE SEARCHED 
   sLookFor = InputBox("Enter Items to Search. Please use comma to separte if multiple items are to be searched", "Search") 
   sLookFor = Trim(sLookFor) 
   If (sLookFor = vbNullString) Then Exit Sub 
    
   'CREATE ARRAY OF WHAT NEEDS TO BE SEARCH 
   vLookFor = Split(sLookFor, ",") 
    
   'PUTTING WHAT NEEDS TO BE SEARCHED IN A DICTIONARY TO REFER LATER 
   Set dicMyItems = CreateObject("Scripting.Dictionary") 
   For lRow = 0 To UBound(vLookFor) 
      sLookFor = Trim(vLookFor(lRow)) 
      If (sLookFor <> vbNullString) _ 
      Then 
         If Not dicMyItems.Exists(sLookFor) _ 
         Then 
            ' A NEW ITEM THAT NEEDS TO BE SEARCHED 
            dicMyItems.Add Key:=sLookFor, Item:=vbNullString 
         End If 
      End If 
   Next lRow 
    
   'IF NOTHING IS TO BE SEARCHED THEN EXIT 
   If (dicMyItems.Count = 0) Then Exit Sub 
    
   'START ROW 
   lRow = 4 
   bSkipThisOrder = False 'DEFAULT THAT DONT SKIP THIS ORDER 
   lStartOrder = lRow 
    
   'LOOP THRU THE SHEET 
   Do While Cells(lRow, "A") <> vbNullString 
      sLookFor = Cells(lRow, "D")        'ITEM ON CURR ORDER 
      sCurrOrder = Cells(lRow, "A")      'GET CURR ORDER 
      sNextOrder = Cells(lRow + 1, "A")  'WHAT IS THE NEXT ORDER IN QUE 
       
      'IF THE CURRITEM IS NOT IN MY LOOKUP LIST THEN MARK TO SKIP THIS ORDER 
      If Not dicMyItems.Exists(sLookFor) _ 
      Then 
         bSkipThisOrder = True 
      End If 
       
      'IF NEXT ORDER WILL NOT BE SAME AS THIS ORDER, THEN SEE IF WE NEED TO COPY OR NOT 
      If (sCurrOrder <> sNextOrder) _ 
      Then 
         If Not bSkipThisOrder _ 
         Then 
            lPasteAt = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row 
            lPasteAt = lPasteAt + 1 
            Application.CutCopyMode = False 
            Rows(lStartOrder & ":" & lRow).Copy 
            Sheets("Sheet2").Cells(lPasteAt, "A").PasteSpecial 
            Application.CutCopyMode = False 
         End If 
         bSkipThisOrder = False 
         lStartOrder = lRow + 1 
      End If 
      lRow = lRow + 1 
   Loop 
   dicMyItems.RemoveAll 
   Set dicMyItems = Nothing 
End Sub

OMG Riz, what a code! No wonder I couldn't find a solution.

PMARI should be pleased.

Best regards,
Trowa