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
Else
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
Next
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.
Does not color the entire range of cell, just a small fraction.