How to highlight Excel duplicates in different colors

It is easy to highlight duplicates in an excel worksheet using the same color by use of conditional formatting, additionally, we can use different colors for every highlight in the same column to make them easily noticeable while working on an excel worksheet. Below are the steps and how to highlight using different colors:

1. Select the column you want to highlight the duplicates in different colors and then press hold the ALT+f11 to open the Microsoft visual basis for application windows.

2. Click insert, then module, and paste he following codes on the module window

VBA mode insert highlights in different color

Sub ColorCompanyDuplicates()

'Updateby Extendoffice 20160704

Dim xRg As Range

Dim xTxt As String

Dim xCell As Range

Dim xChar As String

Dim xCellPre As Range

Dim xCIndex As Long

Dim xCol As Collection

Dim I As Long

On Error Resume Next

If ActiveWindow.RangeSelection.Count > 1 Then

xTxt = ActiveWindow.RangeSelection.AddressLocal


xTxt = ActiveSheet.UsedRange.AddressLocal

End If

Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)

If xRg Is Nothing Then Exit Sub

xCIndex = 2

Set xCol = New Collection

For Each xCell In xRg

On Error Resume Next

xCol.Add xCell, xCell.Text

If Err.Number = 457 Then

xCIndex = xCIndex + 1

Set xCellPre = xCol(xCell.Text)

If xCellPre.Interior.ColorIndex = xlNone Then xCellPre.Interior.ColorIndex = xCIndex

xCell.Interior.ColorIndex = xCellPre.Interior.ColorIndex

ElseIf Err.Number = 9 Then

MsgBox "Too many duplicate companies!", vbCritical, "Kutools for Excel"

Exit Sub

End If

On Error GoTo 0


End Sub

1. Then you press the f5 key on your keypad to run this code, and a prompt box will remind you to select the data range that you want to highlight the duplicate values on.

2. After you have selected the range press the okay button on the pop-up menu and all the duplicate data in the excel worksheet dataset will be highlighted in different colors as shown below.

1 thought on “How to highlight Excel duplicates in different colors”

Leave a Comment