Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Highlight Rows with Conditions met [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello Friends,

I desperately need a macro to Highlight /or separate rows when specific conditions met.

sample file enclosed. Link http://www.speedyshare.com/files/29614659/sample.xls

Any help highly appreciated.

With Best Regards,PM.

1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
Have you looked at conditional formatting ?
Sorry cannot look at speedy share from work but conditonal format has been answered many times
View all 6 comments
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
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.
Report
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
 460
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
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
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
Report
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
 460
OMG Riz, what a code! No wonder I couldn't find a solution.

PMARI should be pleased.

Best regards,
Trowa
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
It was not difficult Trowa. You did the hard part of getting the requirement. I had failed in that miserably and thats 99% of the job. Since you laid it out so nice and crisp, it was easy to see the solution. But thanks for likening the solution.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!