Excel Macro code to Highlight Duplicates from Selection

Share on facebook
Share on linkedin
Share on email
Share on whatsapp

How to Highlight Duplicates from Selection in excel sheet through VBA Macro Code

For using this VBA macro code in excel to Highlight Duplicates from Selection you will have copy below Macro code in Excel Visual Basic Editor

Highlight Duplicates from Selection

Sub HighlightDuplicateValues()
Dim myRange As Range
Dim myCell As Range
Set myRange = Selection
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 36
End If
Next myCell
End Sub

This macro will check each cell of your selection and Highlight Duplicates from Selection.  You can also change the color from the code.

How to Highlight Duplicate Values in Excel using VBA Codes

While working on huge Excel data, duplicate values are always a concern. Most of the time I use to remove duplicate option to remove all those values.  And, I’m sure you must be doing 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.

In this post, we’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…

A. How to Highlight Duplicate Values Within Each Row using VBA Macros

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.

B. How to Highlight Duplicate Values Within Each Column using Excel VBA Macro code

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

C. How to Highlight Duplicate Values Within Selection using Excel VBA Macro code

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

D. How to Highlight Duplicate Values Entire Data using Excel Macro code

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.

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.

What is a Macro Code?

In MS Excel, macro code is a programming code which is written in VBA (Visual Basic for Applications) language.

The idea is to use macro code for automating various activities which you perform manually in Excel, otherwise.

For example, you can use a code to print only a particular range of cells just with a single click instead of selecting the range -> File Tab -> Print -> Print Select -> OK Button.

How to use a Macro Code in Excel

click-on-visual-basic-editor-before-you-use-these-useful-macros-for-excel

  • On the left side in “Project Window”, right click on the name of your workbook and insert a new module.

add-module-to-paste-these-useful-macros-for-excel

  • Just paste your code into the module and close it.

use-useful-macro-codes-examples-by-pasting-them-into-vb-editor

  • Now, go to your developer tab and click on the macro button.

useful-macro-codes-examples-to-use-from-macro-options

  • It will show you a window with a list of the macros you have in your file from where you can run a macro from that list.

useful-macro-codes-examples-list-from-macro-option

 

RECENTLY UPLOADED EXCEL TEMPLATES

Training_Attendance_Sheet_V1.0

Training Attendance Sheet

Whether you’re organizing any training program for an athletic event. A technology course, or a chess tournament, a good attendance …

Download Now
Weekly_Attendance_Sheet_V1.0

Weekly Attendance Sheet

Our “Weekly Attendance Sheet” is simple, compact, and useful an all-purpose attendance tracker. This attendance tracker includes one worksheet with …

Download Now
Student_Attendance_Sheet_Weekly_Monthly

Student Attendance Sheet

These student attendance sheet records is create to help teachers keep track of attendance of their students. There are two …

Download Now
Shift_Planner_with_Attendance_Tracker_Pro-employee

Shift Planner with Tracker

Shift Planner with Tracker for Excel is a powerful spreadsheet that help any Human Resources or operational managers creating their …

Download Now
Employee_Attendance_Tracker_Pro_V2.55

Employee Attendance Tracker

The free employee attendance tracker template is one of my popular excel templates. I have create a more general employee …

Download Now
Islamic_Calendar

Islamic Calendar

The Islamic calendar (it is known also as Muslim calendar or Hijri calendar) is a lunar calendar based on 12 …

Download Now