How to easily compare two Excel Columns

How to compare two Excel columns for differences? How to highlight the differences?

Many times I find myself wanting to compare and see what changed between two excel files with similar content.

Case 1

I got a file in an email from a co-worker that made some changes to a file I was working on?  Doesn’t it happen all the time…?  Now I don’t know what changes he/she made.  So either I reject my changes or I run the risk of losing his/her changes.

Case 2

I saved a file in two different locations and forgot which one has the latest changes.

If you happen to fall into similar circumstances, you should definitely continue reading this post.

Surprisingly, Microsoft Excel (2007-2013) doesn’t provide a way to compare two excel files like it is available in Microsoft Word.  I created my own simple way of comparing two columns in Excel using VBA.  There will be many ways you can do that, but it is my version of comparing two columns and highlight the changes.

Below I have written a Microsoft Visual Basic Code (VBA), a macro, that you can freely use for yourself.  Just copy/paste the code below and save that as a macro file.  If you want you can create a ribbon shortcut so you can get to it quickly…(something I could explain in a future post.)

VBA Code to compare two Excel columns

Option Explicit
Dim i As Integer, j As Integer
Dim Col1 As Range, Col2 As Range
Sub ExcelComparison()
'Prompt for first column range selection...
Set Col1 = Application.InputBox("Select First Column Range to Compare", Type:=8)

'Check if the range is only 1 column...
If Col1.Columns.Count > 1 Then
Do Until Col1.Columns.Count = 1

MsgBox "Please be certain to select only 1 column range."
Set Col1 = Application.InputBox("Select First Column Range to Compare", Type:=8)
Loop

End If

'Prompt for second column range selection...

Set Col2 = Application.InputBox("Select Second Column range to Compare", Type:=8)

'Check if the range is only 1 column...
If Col2.Columns.Count > 1 Then
Do Until Col2.Columns.Count = 1

MsgBox "Please be certain to select only 1 column range"
Set Col2 = Application.InputBox("Select Second column range to Compare", Type:=8)
Loop

End If

'Limit the range sizes to 10000 rows of the active sheet, but this number can be changed depending on your needs

If Col1.Rows.Count > 10000 Then

Set Col1 = Range(Col1.Cells(1), Col1.Cells(ActiveSheet.UsedRange.Rows.Count))
Set Col2 = Range(Col2.Cells(1), Col2.Cells(ActiveSheet.UsedRange.Rows.Count))

End If

'Perform the comparison and set cells that are the same to yellow
'First Run based on Number of Rows in Column 1

For i = 1 To Col1.Rows.Count
For j = 1 To Col2.Rows.Count
If Col1.Cells(i) = Col2.Cells(j) Then

Col1.Cells(i).Interior.Color = vbYellow
Col2.Cells(j).Interior.Color = vbYellow

End If
Next j
Next i
'Second Run based on Number of Rows in Column 2, assuming there are more rows in Column 2 than in Column 1
For j = 1 To Col2.Rows.Count
For i = 1 To Col1.Rows.Count
If Col2.Cells(j) = Col1.Cells(i) Then

Col1.Cells(i).Interior.Color = vbYellow
Col2.Cells(j).Interior.Color = vbYellow

End If
Next i
Next j
MsgBox "Thank you for using this code from @techacker. Your comparison is highlighted."
End Sub 

Please let me know if you find any issues while using this code or if there are any suggestions to improve it and make it simpler. Your suggestions are much appreciated.
If you liked this post, please help me share it with other who may like it too.  Tweet it, Like it on Facebook or share on Google+, whatever you like, you may use the appropriate buttons on the side or on the bottom of this post.

Thanks in advance.