Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

How To Highlight Duplicate Values Using VBA

    highlight duplicate values using vba

    While working with huge data, Duplicate Values are always a concern.

    And, I am sure most of the time you remove them with remove duplicate option. But, removing duplicate values or just counting duplicate values never sort out the problem.

    The important thing is to review all the duplicate values before deletion.

    So, today I will show you how to highlight duplicate values using VBA.

    And here, I have listed 4 different methods to highlight duplicate values.

    1. Check within each row
    2. Check within each column
    3. Check each cell in the selection
    4. Check each cell in entire data

    So Let’s get started.

    1. Check Within Each Row

    You can check duplicate values in a row and highlight them with this macro.

    This will check all cells from a single row and highlight the cells which are duplicate within it. And after that, it will go to the next row and do the same.

    In this way, you can able to highlight cells which are in duplicate in their respective rows.

    check duplicate values in a row

    Sub DuplicateValuesFromRow()
    'Declare All Variables.
    Dim myCell As Range
    Dim myRow As Integer
    Dim myRange As Range
    Dim myCol As Integer
    Dim i As Integer
    'Count Number of Rows and Columns
    myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
    myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
    'Loop Each Row To Check Duplicate Values and Highlight cells.
    For i = 2 To myRow
           Set myRange = Range(Cells(i, 2), Cells(i, myCol))
           For Each myCell In myRange
                If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
                myCell.Interior.ColorIndex = 3
                End If
           Next
    Next
    End Sub

    How does It work?

    If you go through this macro, you will find that I have used a loop system to check each row for duplicate values and  highlight them with a color.

    Things to remember

    • Your data should not have a blank row or column in it. If it is, this macro will not cover the part which across the blank row or column.
    • Starting cell of your data should be A1 cell. And if you want to adjust the starting point you have to adjust the code in the following lines.
    myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count 
    
    myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
    • First row & column of your data sheet should be heading.

    2. Check Within Each Column

    You can check duplicate values in a column and highlight them with this macro.

    This will go to the first column check all cells and highlight the cells which duplicate within the column. And, after that, it comes to the next column & do the same.

    In this way, you can able to highlight cells which are in duplicate in their respective columns.

    Sub DuplicateValuesFromColumns()
    'Declare All Variables
    Dim myCell As Range
    Dim myRow As Integer
    Dim myRange As Range
    Dim myCol As Integer
    Dim i As Integer
    'Count number of rows & column
    myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
    myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
    'Loop each column to check duplicate values & highlight them.
    For i = 2 To myRow
           Set myRange = Range(Cells(2, i), Cells(myRow, i))
           For Each myCell In myRange
                If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
                myCell.Interior.ColorIndex = 3
                End If
           Next
    Next
    End Sub

    Five-Useful-Macros-To-Pullout-Duplicate-Values-From-Data2

    How does It work?

    It works same as the macro we have used for rows. A loop which can check each and every column and then highlight duplicate values in it.

    3. Check Each Cell in the Selection

    You can highlight duplicate values from a custom selection.

    It will check the entire of your selection and highlight all the duplicate values.

    highlight duplicate values from selection

    Sub DuplicateValuesFromSelection()
    Dim myRange As Range
    Dim i As Integer
    Dim j As Integer
    Dim myCell As Range
    Set myRange = Selection
    For Each myCell In myRange
                  If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
                  myCell.Interior.ColorIndex = 3
                  End If
    Next
    End Sub

    How does It work?

    This macro will only check those cells which you have selected and then highlight cells which are in duplicate in the selection.

    Alternate method

    You can also highlight duplicate values with conditional formatting.

    1. Select the range for which you want to check the duplicate values.
    2. Go to Home Tab -> Styles -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.

    highlight duplicate values with conditional formating

    4. Check Each Cell in Entire Data

    If you have a large dataset, you can use this macro to check entire data and highlight duplicate values. It will check each and every cell from the data.

    check entire date & highlight duplicate values

    Sub DuplicateValuesFromTable()
    Dim myRange As Range
    Dim i As Integer
    Dim j As Integer
    Dim myCell As Range
    Set myRange = Range("Table1")
    For Each myCell In myRange
                  If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
                  myCell.Interior.ColorIndex = 3
                  End If
    Next
    End Sub

    How does It work?

    This macro will check all the cells from your data set. And, highlight cells which are in duplicate in the data.

    5. Bonus Tip

    Macro to count duplicate values from a sheet.

    Sub CountDuplicates()
    Dim i As Integer
    Dim j As Integer
    Dim myCell As Range
    Dim myRange As Integer
    myRange = Range("Table1").Count
    j = 0
    For Each myCell In Range("Table1")
                  If WorksheetFunction.CountIf(Range("Table1"), myCell.Value) > 1 Then
                  j = j + 1
                  End If
    Next
    MsgBox j
    End Sub

    This macro will check all the cells from your data and count the duplicate values with a message box.

    Download Sample File

    download sample file to learn how to highlight duplicate values with vba

    Conclusion

    All the above macros are very helpful to check and highlight duplicate values from the data. You can apply them as per your requirement.

    So, now tell me one thing. Do you have any other method to highlight duplicate values? Please share with me in the comment box.

    I would love to hear back from you.

    What’s Next In VBA?

    Here I have a step by step guide to creating a pivot table with VBA. Follow these steps to automate your pivot tables.



    • Farhad Essop

      Good workPuneet-Gogia.It would be great if each set of duplicate values were uniquely highlighted – perhaps in different colours.

      • Puneet Gogia

        Hey Farhad,
        Thanks for your words & suggestion.

        we can do this by creating a loop in color. Let me update this.

        Love PG

    • Inet Kemp

      Thank you Puneet for this

      • Puneet Gogia

        You are welcome Inet.

    • ratanak

      Great work.please highlight each set of duplicate values by different colors it will be more cool

    • ratanak

      Hi puneet could you provide formula for compare duplicates by rows in multiple columns?