VBA example - Excel 2007: highlight duplicates

VBA code to highlight duplicates

In this example, you will learn VBA code to highlight duplicate values in a specified range of cells. To accomplish this task, we will use two VBA for loops to compare values and highlight the cells accordingly. Suppose that you have the following text in your Excel worksheet. In the range of cells there are some duplicate values that you would like to inspect.

VBA Code
VBA for Excel
Learn VBA code
VBA for excel Examples
VBA Loop

To run this VBA example code, you can copy and paste the VBA code below in to the block of Worksheet_Activate() procedure. To open this block, double-click the current worksheet, then in the objects dropdown list select Sheet1 and in events list select Activate.

VBA for Excel: VBA example code to highlight duplicate values

VBA code for this example:

Option Explicit
Private Sub Worksheet_Activate()      
HighLightDuplicates "B1:B10"
End Sub  
Sub HighLightDuplicates(ByVal rng As String)    
Dim i, j As Integer
Dim temp As Variant
    i = 1    
For i = 1 To Selection.Count
temp = Range(Left(rng, 1) & i)
For j = i + 1 To Selection.Count
If temp = Range(Left(rng, 1) & j) And temp <> "" Then
Range(Left(rng, 1) & i).Interior.Color = RGB(0, 100, 255)
Range(Left(rng, 1) & j).Interior.Color = RGB(0, 100, 255)
End If
End Sub


Altaf comment


my comment


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.