This macro works... kind of. Please spot my error.

You are going to type out an array list of over 1000 items? Why not put them in a column in another sheet, then load them from there? I get no errors when I run your code, even if I add some arbitrary items to the array. Not sure what it is doing though.

Greg

Originally Posted by Alias1431

Load them from another sheet? What do you mean by this?

What I'm saying is, if you've got a list of stock codes to process, I'm sure you have them in a document somewhere right? Put that list of stock codes into your workbook. Then, instead of iterating through your array items, you would set the list as a range, and iterate through the range items one by one the same way you are iterating through the array.

Yes, tomorrow. That is, if someone does not beat me to it

Try this:

Sub BPO()
    Dim avsTgt      As Variant
    Dim iTgt        As Long
    Dim rFind       As Range
    Dim sAddr       As String

    avsTgt = Array("ABRB026", "ABRB026A", "ABRB026AA", "ABRB027")

    For iTgt = LBound(avsTgt) To UBound(avsTgt)
        Set rFind = Cells.Find(What:=avsTgt(iTgt), _
                               LookAt:=xlWhole, _
                               MatchCase:=False)
        If Not rFind Is Nothing Then
            sAddr = rFind.Address
            
            Do
                rFind.Interior.Color = vbGreen
                Set rFind = Cells.FindNext(After:=rFind)
            Loop While rFind.Address <> sAddr
        End If
    Next iTgt
End Sub

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

Back to what I mentioned yesterday, about using a range of stock items against which to check orders, rather than an array, you can try this. And, I took lead from shg, in that I did not start with your original code. I think a lot of it can be pared down, so this is a simple starting point. It assumes your list of stock items is in sheet2, and your orders are in sheet1. You can change as needed. The benefits of using a range instead of an array are 1) you are not going to run out of memory if your list of stock items gets too large, and 2) you are not going to have to type in all the values in your array.

Greg

Sub BPO2()

    Dim rngStock As Range
    Dim rngOrders As Range
    Dim WSStock As Worksheet
    Dim WSOrders As Worksheet
    Dim rFind As Range
    Dim c As Variant
    
    Application.ScreenUpdating = False
    
    Set WSStock = Sheets("Sheet2")
    Set WSOrders = Sheets("Sheet1")
    
    Set rngStock = WSStock.Range("A1:A" & WSStock.Cells(Rows.Count, "A").End(xlUp).Row)
    Set rngOrders = WSOrders.Range("A1:A" & WSOrders.Cells(Rows.Count, "A").End(xlUp).Row)
    
    With rngStock
        For Each c In rngOrders
            Set rFind = .Cells.Find(What:=c.Value, _
                                    LookIn:=xlValues, _
                                    MatchCase:=False)
            If Not rFind Is Nothing Then
                c.Interior.Color = vbGreen
            End If
        Next c
    End With

    Application.ScreenUpdating = True
    
End Sub

I'm pretty sure the one I supplied only makes green the values on sheet 1 that are also in the list on sheet 2. If your stock codes are sometimes appended with extra characters, like "A" or "AA", would it make sense to only match on the first N characters? Based on your initial 4 array elements, that would be 7 characters.

Greg

Originally Posted by Alias1431

That's good thinking. Here is a picture I took for you showing the excess green highlights:

http://i.imgur.com/YlKGvMx.png

Can you attach your file? I'm not able to get the code I supplied to behave like that.

Originally Posted by Alias1431

Some stock codes have an A or AA at the end, and I would like to include those possible variations in the search. Thanks for your help, guys!

DOH! As it turns out, the cells.find method as posted will look for any instance of the search term in the range. So, for example, if your current search term is "CODE01", and the List of stock codes includes "CODE01A" only, then it would be found. This works to your advantage in certain situations, but I can also be a detriment, like if you only want to find exact matches. I would recommend looking for exact matches, but that's just me.

Greg

Nope. My company blocks lots of stuff, but I am able to open the files folks attach to posts here. That is probably the only way I'll get it.

Ok, it makes perfect sense now, after seeing your workbook. You had blank cells in the range of stock items. So, when the code encountered a blank cell in the search range, then found a blank cell in the lookup list, it returned a match. If you eliminate all blank rows in your stock item list this will not happen. If, for some reason, you need them in there, I added a non-blank validation. Let me know if this does not solve your issue.

Sub BPO()

    Dim rngStock As Range
    Dim rngOrders As Range
    Dim WSStock As Worksheet
    Dim WSOrders As Worksheet
    Dim rFind As Range
    Dim c As Variant
    
    Application.ScreenUpdating = False
    
    Set WSStock = Sheets("Sheet2")
    Set WSOrders = Sheets("Sheet1")
    
    Set rngStock = WSStock.Range("A1:A" & WSStock.Cells(Rows.Count, "A").End(xlUp).Row)
    Set rngOrders = WSOrders.Range("A1:A" & WSOrders.Cells(Rows.Count, "A").End(xlUp).Row)
    
    With rngStock
        For Each c In rngOrders
            If c.Value <> "" Then
                Set rFind = .Cells.Find(What:=c.Value, _
                                        LookIn:=xlValues, _
                                        MatchCase:=False)
                If Not rFind Is Nothing Then
                    c.Interior.Color = vbGreen
                End If
            End If
        Next c
    End With

    Application.ScreenUpdating = True
    
End Sub

Greg