How to Highlight Duplicates in Excel with Different Colors: 2 Methods

Below, we will go over two quick and useful methods to highlight duplicates in Excel. To explore the methods, we’ll use the following dataset, representing a salesperson’s sales in different regions.

Method 1 – Apply Conditional Formatting to Highlight Duplicates in Excel with Different Colors

Steps:

• Select the data range where you want to apply Conditional Formatting. I selected C5:C11.
• Click: Home > Conditional Formatting > Highlight Cells Rules > Equal To
  • A Conditional Formatting dialog box named Equal To will open

• Type the name of the Region for the duplicates you are looking for. I typed UK in the Format cells that are EQUAL TO:
• Click on the drop-down icon from the right side of the dialog box and select your desired color.
• Press OK.

You will get all the duplicates for the region of the UK highlighted with your selected color.

Highlight the duplicates for another Region – London.

• Follow the first step to open the Conditional Formatting dialog box.
• Type the region name – London in the Format cells that are EQUAL TO:
• Select your desired color from the drop-down list.
• Press OK.

Duplicates of the London region are now highlighted with a different color.

• To highlight the duplicates for the Canada region, follow the first step to open the Conditional Formatting dialog box.
• Write Canada in the Format cells that are EQUAL TO: box and select another color from the drop-down list.
• Press OK.

All different duplicate Regions are highlighted with different colors.

Read More: How to Highlight Duplicates in Multiple Columns in Excel

Method 2 – Embed Excel VBA to Highlight Duplicates with Different Colors

Using VBA macros is quicker than the first method.

Steps:

• Right-click on the sheet title to open the VBA window.

• Copy the following codes to the VBA window.

Sub Duplicates_Dif_Colors()
    Dim RG As Range
    Dim TT As String
    Dim CL As Range
    Dim CR As String
    Dim CP As Range
    Dim CD As Long
    Dim Cltn As Collection
    Dim J As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      TT = ActiveWindow.RangeSelection.AddressLocal
    Else
      TT = ActiveSheet.UsedRange.AddressLocal
    End If
    Set RG = Application.InputBox("Select the range of data:", "Duplicates with Colors", TT, , , , , 8)
    If RG Is Nothing Then Exit Sub
    CD = 2
    Set Cltn = New Collection
    For Each CL In RG
      On Error Resume Next
      Cltn.Add CL, CL.Text
      If Err.Number = 457 Then
        CD = CD + 1
        Set CP = Cltn(CL.Text)
        If CP.Interior.ColorIndex = xlNone Then CP.Interior.ColorIndex = CD
        CL.Interior.ColorIndex = CP.Interior.ColorIndex
      ElseIf Err.Number = 9 Then
        MsgBox "Found excessive duplicates", vbCritical, "Duplicates with Colors"
        Exit Sub
      End If
      On Error GoTo 0
    Next
End Sub

• Click on the Run icon to run the codes.

An lnputBox will pop up to select the data range.

• Select the data range C5:C11 by dragging it with your mouse.
• Press OK.

Excel has highlighted all the duplicates with different fill colors.

Read More: [Fix:] Highlight Duplicates in Excel Not Working

Practice Section

You will get a practice section in the Excel file given above to practice the explained methods.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Related Articles

• How to Highlight Duplicates but Keep One in Excel
• Highlight Cells If There Are More Than 3 Duplicates in Excel
• How to Highlight Duplicates in Two Columns in Excel
• How to Highlight Duplicates in Two Columns Using Excel Formula
• How to Highlight Duplicate Rows in Excel
• Highlight Duplicates across Multiple Worksheets in Excel

<< Go Back to Highlight Duplicates in Excel | Learn Excel