# Conditional Formatting Duplicates Based on Number of Duplicates

#### snewsome

##### New Member
Hey Everyone,

I'm having a hard time finding a solution to finding duplicates based on the number of duplicates found. I know how to setup CF to find duplicates but what if I want to change the CF to highlight the cells depending on how many times they're duplicated. EXAMPLE: if a double match is found highlight Yellow, if a triple match is found highlight orange....etc. Hope this makes sense. I'm trying to create a 2,3,4, and 5 duplicate CF. Hoping this is possible.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Jake Blackmore

##### Board Regular
Hello,

If you really wanted to use conditional formatting - please use it in moderation. If you copy and paste conditional formatting for multiple criteria in many cells it is a lot for Excel to calculate and it will invoke calculation every time you do anything. There are alternative solutions...

My helper column in column B works off my criteria in column A:
I have headers in Row 1
B2=COUNTIF(\$A\$1:A2,A2)
This provides me with the occurrence number as you referred to.

Now I can create conditional formatting on column A 'Use a formula to determine which cells to format'.
Formula: =B2:B5=1
Applies to: =\$A\$2:\$A\$5

so on each cell in the formula array if the criteria is 1 then I have chosen a colour and applied this check to the respective range in column A. This method can now be duplicated to set up colours for each criteria (2, 3 etc..). I would consider dynamic named ranges to make this more flexible.

May I ask why you would want to do this?

#### steve the fish

##### Well-known Member
Use a CF formula such as:

=AND(A1<>"",COUNTIF(A:A,A1)=2)

Repeat for =3, =4 and =5. What will you be doing for >5?

#### lrobbo314

##### Well-known Member
Here is a VBA solution that could be easily modified if you wanted to account for even more duplicates.

Code:
``````Const Red = 242
Const lRed = 7171583
Const Orange = 1019133
Const Yellow = 458488
Const Green = 917365

Sub cf5()
Dim r As Range
Dim cel As Range

Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))

For Each cel In r
With cel.Interior
Select Case Application.WorksheetFunction.CountIf(r, cel)
Case 1
.Color = Green
Case 2
.Color = Yellow
Case 3
.Color = Orange
Case 4
.Color = lRed
Case 5
.Color = Red
End Select
End With
Next cel

End Sub``````

Replies
2
Views
376
Replies
2
Views
484
Replies
5
Views
263
Replies
5
Views
256
Replies
3
Views
362

1,195,950
Messages
6,012,487
Members
441,701
Latest member
vnkendijs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back