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
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




Comments

Name:
Comment:
Enter the text:
CAPTCHA image

...................................................................................................................................Home | Forum | About | Contact
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.
Copyright @ 2011-2013 worldbestlearningcenter. All Rights Reserved.
Home Programming Web DB & App Questions Exercises Tips Programs FAQs Download About
Popular Pages
You might like