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
Join Our WhatsApp Group
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.
