How to Compare Two Ranges using Macros (VBA) in Excel 365?
Tags: comparetworangesusin, vbamacros, macrosvba
Published: 2021-02-26 08:54:13

---
title: How to Compare Two Ranges using Macros (VBA) in Excel 365?
published: true
date: 2021-02-26 08:52:48 UTC
tags: Comparetworangesusin,VBAMacros,MacrosVBA
canonical_url: https://geekexcel.com/how-to-compare-two-ranges-using-macros-vba-in-excel-365/
---

If you want to [compare two ranges](https://geekexcel.com/compare-two-ranges-to-select-shade-duplicate-or-unique-values-in-excel-365/) 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](https://geekexcel.com/excel-office-365-vba-overview-definition-and-examples/)) 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.

![Input ranges](https://geekexcel.com/wp-content/uploads/2021/02/mspaint_cVX5BhEGa2.png)<figcaption id="caption-attachment-22166">Input ranges</figcaption>

- On the **Developer Tab, select** the **Visual Basic** option under the **Code** section.

![Select Visual Basic option](https://geekexcel.com/wp-content/uploads/2020/09/Select-Visual-Basic-option-4.png)<figcaption id="caption-attachment-14755">Select the Visual Basic option</figcaption>

- 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.**

![Save the code](https://geekexcel.com/wp-content/uploads/2021/02/EXCEL_3n8ik5lH3T.png)<figcaption id="caption-attachment-22167">Save the code</figcaption>

- You have to open the sheet containing the data. On the **Developer Tab, choose** the **Macros option** in the **Code** section.

![Choose the Macros option](https://geekexcel.com/wp-content/uploads/2020/09/Choose-the-Macros-option-2.png)<figcaption id="caption-attachment-14747">Choose the Macros option</figcaption>

- You need to make sure that your macro is selected and **click** the **Run** button.

![Click the Run button](https://geekexcel.com/wp-content/uploads/2021/02/EXCEL_BwI9OCjTQR.png)<figcaption id="caption-attachment-22168">Click the Run button</figcaption>

- Then you need to **select the first range** and **click** the **OK** button.

![Select the first range](https://geekexcel.com/wp-content/uploads/2021/02/EXCEL_YZcvfS18AB.png)<figcaption id="caption-attachment-22169">Select the first range</figcaption>

- Now, you have to **select** the **second range** and **hit** the **OK** button.

![Select the second range](https://geekexcel.com/wp-content/uploads/2021/02/EXCEL_JDWhvJZ7uk.png)<figcaption id="caption-attachment-22170">Select the second range</figcaption>

- At last, you will see that all the duplicate values have been highlighted as shown in the below image.

![Output](https://geekexcel.com/wp-content/uploads/2021/02/EXCEL_rgkImbJdnH.png)<figcaption id="caption-attachment-22171">Output</figcaption>

## 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.](https://geekexcel.com/)** Keep Learning!

#### Read Also:

- [Deselect Cells from Selected Range using Macros (VBA) in Excel 365!!](https://geekexcel.com/deselect-cells-from-selected-range-using-macros-vba-in-excel-365/)
- [How to Copy a Cell to Clipboard using Macros (VBA) in Excel 365?](https://geekexcel.com/how-to-copy-a-cell-to-clipboard-using-macros-vba-in-excel-365/)