Compare columns from two excel workbooks and highlight only the difference

sugaprasad

New Member
Joined
Sep 4, 2013
Messages
9
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,611
Messages
5,625,822
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top