Vba To find unmatched Data in two workbooks

[bboysen]

Ok so i have looked and looked and looked but cant come up with anything on the internet. So now I turn to you.

What i need to do is compare two different workbooks to find the unmatched info in Workbook A.

Example of WorkBook A


[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Code[/TD]
[TD="width: 64"]Note 1[/TD]
[TD="width: 64"]Note 2[/TD]
[TD="width: 64"]Note 3[/TD]
[/TR]
[TR]
[TD]WELD[/TD]
[TD="align: right"]1123[/TD]
[TD="align: right"]1145[/TD]
[TD="align: right"]1178[/TD]
[/TR]
[TR]
[TD]WELD[/TD]
[TD="align: right"]1132[/TD]
[TD="align: right"]1147[/TD]
[TD="align: right"]1149[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BEND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WELD[/TD]
[TD="align: right"]1156[/TD]
[TD="align: right"]1187[/TD]
[TD="align: right"]1173

[/TD]
[/TR]
</tbody>[/TABLE]


Example of WorkBook B

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]NOTE 1[/TD]
[TD="width: 64"]NOTE 2[/TD]
[TD="width: 64"]NOTE 3[/TD]
[/TR]
[TR]
[TD="align: right"]1123[/TD]
[TD="align: right"]1145[/TD]
[TD="align: right"]1178[/TD]
[/TR]
[TR]
[TD="align: right"]1132[/TD]
[TD="align: right"]1147[/TD]
[TD="align: right"]1149[/TD]
[/TR]
[TR]
[TD="align: right"]1156[/TD]
[TD="align: right"]1198[/TD]
[TD="align: right"]1173[/TD]
[/TR]
</tbody>[/TABLE]


I would like to highlight it in workbook a for the bad one
I found this online and it works only if they are in the same rows

Option Explicit


Sub test()


    Dim varSheetA As Variant
    Dim varSheetB As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long


    strRangeToCheck = "A1:IV65536"
    ' If you know the data will only be in a smaller range, reduce the size of the ranges above.
    Debug.Print Now
    varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
    varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
    Debug.Print Now


    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
            If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
                ' Cells are identical.
                ' Do nothing.
            Else
                ' Cells are different.
                Cells(iRow, iCol).Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                    With Selection.Font
                        .Color = -16711681
                        .TintAndShade = 0
                    End With
            End If
        Next iCol
    Next iRow


End Sub

 

[JLGWhiz]

This assumes each workbook uses sheet 1 as the relevant sheet. You will need to edit both the workbook names and the sheet names to suit your purposes. Another assumption is that you are only interested in the 'WELD' items.

Sub comp()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
Set wb1 = Workbooks(1) 'Edit workbook name
Set wb2 = Workbooks(2) 'Edit workbook name
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
    For Each c In rng
        If UCase(c.Value) = "WELD" Then
            Set fVal = Intersect(sh2.Columns(1), sh2.UsedRange).Find(c.Offset(0, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not fVal Is Nothing Then
                    n1 = c.Offset(0, 1).Value
                    n2 = c.Offset(0, 2).Value
                    n3 = c.Offset(0, 3).Value
                    d1 = fVal.Value
                    d2 = fVal.Offset(0, 1).Value
                    d3 = fVal.Offset(0, 2).Value
                    If n1 <> d1 Or n2 <> d2 Or n3 <> d3 Then
                        c.Resize(1, 4).Interior.ColorIndex = 6
                    End If
                End If
        End If
    Next
End Sub