How to Highlight Duplicate Values in Excel using VBA Codes

While working with huge data, duplicate values are always a concern.

Most of the time I use remove duplicate option to remove all those values.

And, I’m sure you do the same.

But removing these duplicate values or just counting them never sort out the problem.

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

Yes, that’s right.

Once you highlight all those values, you can check and then you can delete them.

Today, in this post, I’d like to share with you 4 different VBA codes to highlight duplicate values.

And the part is, these codes can highlight cell using different ways. You can simply copy-paste them in your VBA editor and use them.

So let’s get started…

                                                         

Quick Navigation
1. Within Each Row
2. Within Each Column
3. Within Selection
4. Entire Data

1. Within Each Row

This VBA code checks all the cells from a row and highlights all the cells which are duplicate within a row.

In simple words, if a row has the value “522” twice then it will be considered as duplicate. But if the another 522 is in another row then it will be considered as unique.

highlight duplicate values from each row using VBA code
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

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

Important Points

  1. Your data should not have a blank row or column in it, otherwise, it ignores that cell.
  2. Starting cell of your data should be “A1” cell. And if you want to adjust the starting point you have to adjust the code.
  3. First row and column of your data sheet should be heading.

2. Within Each Column

This VBA code checks all the cells from a column and highlights all the cells which are duplicate within each column.

In simple words, if a column has the value “231” twice then it will be considered as duplicate. But if the another “231” is in another column then it will be considered as unique.

highlight duplicate values from each column using VBA code
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

3. Within Selection

Let’s say you just want to highlight cells with duplicate values from the selection, this code can help you in this.

To use this code you just need to select a range of cells and run this code. It checks each cell with the selection and highlights it with red color if a cell has a duplicate value.

highlight duplicate values from the selection using VBA code
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

4. Entire Data

If you have a large data-set, you can use this macro code to check the entire data and highlight duplicate values.

This code loops through each cell one by one and applies red color all those cells which are the duplicate.

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

Important Note: In the above code, we have used the table name "Table1", you can change this name from the code. Or, if you have a range then you can simply use range name. 

[Bonus Tip] Count Duplicate Values

This code helps you to count the numbers of duplicate values from the selection. When you run this code, it returns a message box which shows the count.

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

Again in the above code, we have used table name and you change it or replace it with a range.

Sample File

Download this sample file from here to learn more.

Conclusion

In different situations, we need check duplicate values in the different ways. And, all the above codes can be helpful for you in this.

If you want you can change the highlight color from the codes using color index number.

Now help me on one thing.

Where can we improve these codes?

Please share your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends.

Share0
Tweet0

Must Read Excel Tutorials

  1. Highlight Blank Cells: These are 3 popular ways to count all the highlight cell which are blank. If a cell has a space then these methods [...] 
  2. Count Unique Values: Just like duplicate values, you can count unique values using a simple formulas + VBA methods [...]
  3. 100 Macro Codes: These useful macro codes can help you save your a ton of time everyday by automating your tasks [...]
  4. Count Cells with Text: These are 6 simple ways to count all those cells where the value is a text. You can use any of them according to [...]
  5. Count Words: Using these 5 ways we can count words from a cell or from a range of cells or ven from the entire worksheet in Excel [...]
  6. Hide Formula from a Cell: Sometimes you simply don’t want to show a formula to others. In Excel, there is a [...]

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn
YouTube

15 thoughts

Leave a Comment

Your email address will not be published.

  2. Your code may be a bit old. It does not appear to work on Excel Office 365. I had to locate code from elsewhere.

    Reply

  3. I was looking for good ‘looping though a column for duplications’ routine (your section 2 above) which is very good and just what I was looking for however, there is a slight mistake as you have a variable ‘myCol’ to count the number of columns but you didn’t use the variable to create a loop such that the routine will continue to check for duplicates how ever many columns there are in your worksheet instead of stopping once you reach the last column of your data.

    Useful bit of code though 🙂

    Reply

  4. thanks Mr Puneet
    in one condition you mentioned that column and row should be headings.
    it’s ok with column but what if my row doesn’t have headings, consider the row starts from a1

    Reply

  6. Good blog, where did you come up with the knowledge in this piece of content? I’m glad I found it though, ill be checking back soon to see what other articles you have.

    MS Excel

    Reply

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

    Reply

    • Hey Farhad,
      Thanks for your words & suggestion.

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

      Love PG

      Reply