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

Leave a Comment