VBA Code to Highlight Unique Values in Excel

VBA code that you can use to highlight unique values in Excel.

VBA Code:

Sub HighlightUniqueValues()
    Dim rng As Range
    Dim cell As Range
    Dim uniqueValues As Collection
    ' Set the range where you want to find unique values
    Set rng = Range("A1:D10")
    ' Create a collection to store unique values
    Set uniqueValues = New Collection
    ' Loop through each cell in the range
    On Error Resume Next ' Skip error if duplicate value is added to the collection
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            uniqueValues.Add cell.Value, CStr(cell.Value)
        End If
    Next cell
    On Error GoTo 0 ' Reset error handling
    ' Highlight the cell(s) with unique values
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            If CountOccurrence(uniqueValues, cell.Value) = 1 Then
                cell.Interior.Color = RGB(255, 255, 0)
            End If
        End If
    Next cell
    ' Inform the user that the highlighting is complete
    MsgBox "The cell(s) with unique values have been highlighted!"
End Sub

Function CountOccurrence(col As Collection, val As Variant) As Long
    Dim i As Long
    Dim count As Long
    count = 0
    For i = 1 To col.Count
        If col(i) = val Then
            count = count + 1
        End If
    Next i
    CountOccurrence = count
End Function

Check All VBA Codes

Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group
Nazim Khan - Author Image

Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]

Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.

Leave a Comment