Excel

How to Highlight Duplicate Data in Red Using Excel VBA’s CountIf Function

 

Data analysis using Excel is essential in many business situations. However, finding duplicate information among large datasets can be a challenge. In this article, we’ll show you how to use Excel VBA to instantly highlight duplicate data in red. This will make organizing your data much more efficient!

Contents
  1. What Does “Highlight in Red” Mean?
  2. Explanation of the VBA Code
  3. Try It Yourself
  4. Conclusion

What Does “Highlight in Red” Mean?

Let’s first see how the Excel sheet visually changes. When duplicate data is entered in the range from A1 to E2, the duplicated cells will be filled with red.

Excel image showing cells with duplicate entries highlighted in red

This visual change is extremely effective for quickly identifying duplicate data.

Explanation of the VBA Code

Next, let’s take a look at the VBA code that enables this functionality. This code runs whenever data in the worksheet is changed. It uses the CountIf function to check if the same value exists within a specified range, and highlights the cell in red if a duplicate is found.

The key here is how to write VBA code that can efficiently and accurately detect duplicates. By using loops and conditional branches, this simple yet powerful code achieves the task. 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim h As Long
    Dim i As Long
    
    For h = 0 To 1
        For i = 1 To 5
            If WorksheetFunction.CountIf(Range("A1:E2"), Cells(1 + h, i)) > 1 Then
                Cells(1 + h, i).Interior.ColorIndex = 3
            Else
                Cells(1 + h, i).Interior.ColorIndex = 0
            End If
        Next i
    Next h
    
End Sub

Worksheet_Change() is triggered whenever changes are made within the Excel sheet. The code uses CountIf to check for duplicate values in the range A1:E2, and highlights duplicates in red while clearing the fill for non-duplicates.

Try It Yourself

To deepen your understanding, try this code yourself. You can download a sample file from the link below. After downloading, open it in Excel, paste the code into the VBA editor, and you can test it immediately.

yo1tec/VBA-CountIf

Conclusion

Excel VBA can significantly streamline data processing tasks. By using the technique introduced here, you can automate duplicate data checks and improve the speed and accuracy of your work. Even if you’re new to programming, give it a try based on this article. It may open up new possibilities for your Excel usage.

　

* Please use at your own risk if repurposing this content.

CountIf macro