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
VBA
VBA Code
Excel
VBA for Excel
Learn VBA code
VBA for excel Examples
VBA Loop
Excel



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
   
Range(rng).Select
    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
           
      
Next
      
          
Next
 
End Sub


HTML Comment Box is loading comments...



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.