If you want to compare two ranges in two different worksheets, what will you do? Here, I am going to share with you the easiest way to compare two ranges using Macros (VBA) in Excel Office 365. Macros can help us to do our tasks efficiently. Let’s get into this article.
Compare Two Ranges using Macros:
To compare two ranges of data in Excel, follow the below steps.
- Let’s learn this with an example. In the below screenshot, you can see two input ranges such as A1:D3 in Sheet 1 and A5:D7 in Sheet 2.
- We are going to compare these two ranges and highlight the duplicate values.
- On the Developer Tab, select the Visual Basic option under the Code section.
- Then you need to copy and paste the code given below.
VBA code to compare two ranges using Macros
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
- You need to save the code by selecting it. Then close the window.
- You have to open the sheet containing the data. On the Developer Tab, choose the Macros option in the Code section.
- You need to make sure that your macro is selected and click the Run button.
- Then you need to select the first range and click the OK button.
- Now, you have to select the second range and hit the OK button.
- At last, you will see that all the duplicate values have been highlighted as shown in the below image.
Closure:
In the above article, you can clearly learn the simple steps to compare two ranges using Macros (VBA) in Excel Office 365. Leave your suggestions in the below comment section. Thanks for visiting Geek Excel. Keep Learning!
Top comments (0)