This post will guide you how to compare two ranges in one worksheet with VBA Macro code and highlight the cells that are different. How do I compare two ranges in two worksheets Using VBA in Excel.
Compare Two Ranges with VBA
Assuming that you have two lists of data in two worksheets, and you want to compare those two ranges in different two worksheets and find out non-matching cells and then highlight it. How to do it. You can try to use an Excel VBA Macro code to compare two selected ranges in two worksheets. Here are the steps:
#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
#4 paste the below VBA code into the code window. Then clicking “Save” button.
Sub CompareTwoRanges() Set myRange1 = Application.InputBox("Select the first Range:", "CompareTwoRanges", "", Type:=8) Set myRange2 = Application.InputBox("Select the second Range:", "CompareTwoRanges", Type:=8) For Each c1 In myRange1 For Each c2 In myRange2 If c1.Value = c2.Value Then c1.Interior.ColorIndex = 38 Exit For End If Next Next End Sub
#5 back to the current worksheet, then run the above excel macro. Click Run button.
#6 Select the first range in Sheet2 worksheet. Click Ok button.
#7 Select the second range in Sheet6 worksheet. Click Ok button.
You should notice that all duplicate values have been highlighted with background color you set.