Compare two worksheet and highlight duplicate values

  How to Compare two worksheet and highlight duplicate values in excel

Sometimes we need to compare two worksheet. Besically We can compare or highlight duplicate value by using conditional formatting. Today we will discuss for two different worksheet. Very easy and simple vba code. Insert a module and write the below vba code. My recomendation first understand yourself then use this so that you can use it in different way.

first define the worksheets which are to be compare and set it by worksheet names

Then define two variable which are i and myRange where myRange is for those range which are going to compare ( Here I want to compare with sheet2's B2:B200 ) and i is a variable which helps you to evalute to compare the each cell value.

And finally use countif formula to identify the duplicate value. If it is greater than 0 then values are duplicate

Highlighted cells are duplicate which are compare with sheet1

Sub highlight_duplicates()

    Dim sh, shp As Worksheet

    Set sh = Sheets("Sheet1")

    Set shp = Sheets("Sheet2")

    Dim i As Long

    Dim myRange As Range, x As Range

    Set myRange = sh.Range("B2:B200")

    For i = 2 To shp.Range("A" & Rows.Count).End(xlUp).Row

    If WorksheetFunction.CountIf(myRange, shp.Cells(i, "B")) > 0 Then

    shp.Cells(i, 2).Interior.ColorIndex = 6 ' for yellow

    End If

    Next

End Sub

Sub Removehighlight()

    Dim shp As Worksheet

    Set shp = Sheets("Sheet2")

    shp.Cells.Interior.ColorIndex = 2

End Sub

Click here to download the file    View more videos & Subscribe my Channel Please if didn't

For Support and Feedback  

Leave your valuable feedback here