Compare columns from two excel workbooks and highlight only the difference

[sugaprasad]

Hi Everyone,

I am trying to compare columns from two different workbooks and highlight the difference.

What I did so far:
I was able to compare the columns and write if its match or no match in a column under second workbook

What I am looking help for:
While comparing two columns I am in need of highlighting the exact difference between the first and second workbook and write that in a column in second workbook.

For example
V1result (cell value from first workbook) is "My Name is Peter"
V2result (cell value from second workbook) is "My Name is Philip"

My expectation is "My Name is Philip" should be written into new column, thus identifying what's the exact difference.

Could you please help me? It would be wonderful. Thank you in advance.

Sub match_columns()
   
    Dim wbWorkbookOne As Workbook
    Dim wbWorkbookTwo As Workbook
    Dim wbWorkbookThree As Workbook
   
Dim I, total, fRow As Integer
Dim found As Range
Dim V1result As String
Dim V2result As String
Dim Ver1sheet As String
Dim Ver2sheet As String


'If there are more than 1 workbooks open - error out and generate message
If Workbooks.Count > 1 Then
    MsgBox "Macro Terminated - Please close other excel to avoid interruption.", vbCritical Or vbOKOnly, "ERROR"
    End
End If

Set wbWorkbookOne = Workbooks(1)



Ver1sheet = wbWorkbookOne.Sheets("Sheet1").Range("B2")
Ver2sheet = wbWorkbookOne.Sheets("Sheet1").Range("B3")

    If Ver1sheet = "" Then
            MsgBox "Ver1 sheet path cannot be empty. Please click on Select button to choose the path where Ver1 sheet is stored", vbExclamation, "Input Error"
            GoTo Over
    End If
   
    If Ver2sheet = "" Then
            MsgBox "Ver2 sheet path cannot be empty. Please click on Select button to choose the path where Ver2 sheet is stored", vbExclamation, "Input Error"
            GoTo Over
    End If

Workbooks.Open (Ver1sheet)
Workbooks.Open (Ver2sheet)

Set wbWorkbookTwo = Workbooks(2)
Set wbWorkbookThree = Workbooks(3)

total = wbWorkbookTwo.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To total
    V1result = wbWorkbookTwo.Sheets("Sheet1").Range("B" & I).Value
    V2result = wbWorkbookThree.Sheets("Sheet1").Range("B" & I).Value
   

If V1result <> V2result Then
   

    wbWorkbookThree.Sheets("Sheet1").Range("C" & I).Value = "Version1 code output - " & V1result & Chr(10) & "Version2 code output - " & V2result
    wbWorkbookThree.Sheets("Sheet1").Range("C" & I).Characters(WorksheetFunction.Find(V1result, Range("C" & I).Value, 1), Len(V1result)).Font.Bold = True
    wbWorkbookThree.Sheets("Sheet1").Range("C" & I).Characters(WorksheetFunction.Find(V1result, Range("C" & I).Value, 1), Len(V1result)).Font.Color = RGB(255, 0, 0)
   
    wbWorkbookThree.Sheets("Sheet1").Range("C" & I).Characters(WorksheetFunction.Find(V2result, Range("C" & I).Value, 1), Len(V2result)).Font.Bold = True
    wbWorkbookThree.Sheets("Sheet1").Range("C" & I).Characters(WorksheetFunction.Find(V2result, Range("C" & I).Value, 1), Len(V2result)).Font.Color = RGB(0, 171, 102)
   
   
    wbWorkbookThree.Sheets("Sheet1").Range("D" & I).Value = "NO MATCH"
    wbWorkbookThree.Sheets("Sheet1").Range("D" & I).Interior.Color = RGB(255, 0, 0)
Else

    wbWorkbookThree.Sheets("Sheet1").Range("D" & I).Value = "MATCH"

End If
Next I

MsgBox "complete"

Over:

End Sub

 

[sugaprasad]

Hi. any help is much appreciated.

 

[mumps]

Rather than trying to decipher your code, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your two sheets that need to be compared. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).