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