Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

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?

  • Serge Bloch

    Hi, Thank you for the good work.
    There is a typo in the link “What’s next in BBA?”.
    It should read: http://excelchamps.com/blog/vba-to-create-pivot-table/