Highlight cells with duplicate values
This tutorial shows how to highlight cells that contain duplicate values in a selected range using Excel or VBA
Select data > Home tab > Style group > Click on Conditional Formatting > Highlight Cells Rules > Select Duplicate Values > Select Duplicate > Select color > Click OK
1. Select the range in which you want to highlight duplicate values. Note: in this example we are selecting range B3:C9. |
2. Select the Home tab. |
3. Click on Conditional Formatting in the Style group. 4. Select Highlight Cells Rules. 5. Select Duplicate Values. |
6. Ensure that Duplicate is selected in the first drop down menu. 7. Select the cell format that you want to apply by selecting the options from the second drop down menu. Note: in this example we are using a Custom Format option. |
8. Select the Fill tab. 9. Select a color. 10. Click OK. Note: in this example we are only applying the fill condition to the cells that contain duplicate values in the selected range. |
11. Click OK. |
Highlighted cells that contain duplicate values in the selected range. |
METHOD 1. Highlight cells with duplicate values using VBA
VBA
Sub Highlight_Duplicate_Values()
'declare variables
Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range
Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range
Set ws = Worksheets("Analysis")
Set ColorRng = ws.Range("B3:C9")
Set ColorRng = ws.Range("B3:C9")
'highlight cells that contain duplicate values in the selected range
For Each ColorCell In ColorRng
For Each ColorCell In ColorRng
If WorksheetFunction.CountIf(ColorRng, ColorCell.Value) > 1 Then
ColorCell.Interior.Color = RGB(220, 230, 241)
Else
ColorCell.Interior.ColorIndex = xlNone
End If
ColorCell.Interior.Color = RGB(220, 230, 241)
Else
ColorCell.Interior.ColorIndex = xlNone
End If
Next
End Sub
ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet which contains the data in which you want to highlight duplicate values by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Highlight Range: Select the range in which to highlight duplicate value by changing the range ("B3:C9") in the VBA code.
Highlight Color: Select the color to highlight the duplicate value in the selected range by changing the RGB code (220, 230, 241) in the VBA code.
Worksheet Selection: Select the worksheet which contains the data in which you want to highlight duplicate values by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Highlight Range: Select the range in which to highlight duplicate value by changing the range ("B3:C9") in the VBA code.
Highlight Color: Select the color to highlight the duplicate value in the selected range by changing the RGB code (220, 230, 241) in the VBA code.
EXPLANATION
This tutorial shows how to highlight cells that contain duplicate values in a selected range using Excel or VBA.
This tutorial provides one Excel method that can be applied to highlight cells that contain duplicate values in a selected range by using the Conditional Formatting. This is accomplished in 11 steps.
This tutorial provides one VBA method that can be applied to highlight cells that contain duplicate values. In this example the VBA code will loop through each cell in the nominated range and will highlight each cell that contains a duplicate value.
RELATED TOPICS
Related Topic | Description | Related Topic and Description |
---|---|---|
Highlight cells with unique values | How to highlight cells that contain unique values in a selected range | |
Color blank cells | How to highlight blank cells | |
Color non blank cells | How to highlight non blank cells | |
Count duplicate values | How to count duplicate values in a range | |
Find duplicate values in a range | How to find duplicate values in a range |