VBA - Do a lookup/comparison, return number of mismatches, highlight mismatches.

[jardenp]

Column A in Book1-Sheet1 has a mix of blanks and values. Many of the values will be "U" followed by a 9-digit number starting with "1" (for example U101222333). In Book 2-Sheet1 Column A there is a master list of "U1" numbers. I can't figure out how to compare the Book1 "U1" numbers to the Book2 master list. I would like VBA code to 1) return (say to variable "U1errors") the number of "U1" numbers in Book1-Column A that are not in Book2-Column A and 2) highlight the cells in Book1-Column A that contain "U1" numbers not found in Book2.

Any suggestions? I can think of a really messy way to get the number of errors, but the highlighting is beyond my modest skills. Besides, this is the place to come to for elegant alternatives to my messy code!

Thanks,

JP in IN

 

[AliMass]

So you have a list of U-number values in one file, and a master list of the same number in the second. You want to know how many of these values are in the first file that are not in the second, and highlight them?

If so, then a MATCH formula plus conditional formatting sounds like a solution. Can you show some examples in case I'm misunderstanding?

 

[jardenp]

Book1 Column A:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Load #
[/TD]
[/TR]
[TR]
[TD]U100000001[/TD]
[/TR]
[TR]
[TD]U100000002[/TD]
[/TR]
[TR]
[TD]U100000003[/TD]
[/TR]
[TR]
[TD]U100000004[/TD]
[/TR]
</tbody>[/TABLE]

Book2 Column A:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Load #[/TD]
[/TR]
[TR]
[TD]U100000001[/TD]
[/TR]
[TR]
[TD]U100000003[/TD]
[/TR]
[TR]
[TD]U100000005[/TD]
[/TR]
[TR]
[TD]U100000006[/TD]
[/TR]
</tbody>[/TABLE]

The VBA code would set a variable named U1Errors, to 2 (because U100000002 and U100000004 are not in Book2) and highlight A3 and A5 in Book1 Column A (because that's where U100000002 and U100000004 are).

Thanks!

 

[AliMass]

If you didn't want this in VBA, you could use a combination of MATCH formula and conditional formatting to get those values and highlight. But it seems you want to populate a variable in VBA, so how about the code below (I used yellow highlight). See if you can follow it and let me know if you have any questions. You'll need to modify this to fit into your code (such as how you define the two lists).

Sub count_highlight()

    Dim objCurrentList As Object
    Dim objMasterList As Object
    Dim objTestCell As Object
    Dim objFoundCell As Object
    Dim U1Errors As Integer 'I would normally put int in front of integer variables
    
    'Set Master List to test against
    Set objMasterList = Workbooks("Book2").Sheets("Sheet1").Range("A2:A5")
    
    'Set list that you want to test
    Set objCurrentList = Workbooks("Book1").Sheets("Sheet1").Range("A2:A5")
    
    'Initialize Error Count (shouldn't be necessary, but good practice)
    U1Errors = 0
    
    'Go through each item in the current list
    For Each objTestCell In objCurrentList
        
        'See if the current item matches an item in the master list
        Set objFoundCell = objMasterList.Find(What:=objTestCell, _
            After:=objMasterList.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=True, SearchFormat:=False)
        
        If objFoundCell Is Nothing Then
            'No match find, so count and highlight
            U1Errors = U1Errors + 1
            objTestCell.Interior.ColorIndex = 6
        Else
            'Match was found, so remove highlighting (in case it was there from before)
            objTestCell.Interior.ColorIndex = 0
        End If
    
    Next objTestCell
    
    'Message box for # of errors (plus grammar).
    MsgBox ("Found " & U1Errors & " mismatch" & IIf(U1Errors <> 1, "es.", "."))


End Sub