Md. Sourov Hossain Mithun
Sometimes we need to find duplicates and mark them with different colors for many purposes. Also, it helps to analyze data. Excel has some useful features to do it. In this article, I’ll show two quick and useful methods to highlight duplicates in Excel with different colors with easy steps, and clear screenshots.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
2 Ways to Highlight Duplicates in Excel with Different Colors
To explore the methods, we’ll use the following dataset that represents some salesperson’s Sales in different Regions. Have a look that some region names are repeated, we’ll highlight those duplicate regions with different colors.
1. Apply Conditional Formatting to Highlight Duplicates in Excel with Different Colors
First, we’ll learn how to use Conditional Formatting for highlighting duplicates with different colors. Conditional Formatting has many customizations to highlight data. But the limitation is that we’ll have to use separate Conditional Formatting for every different duplicate to get different colors.
Steps:
- Select the data range where you want to apply Conditional Formatting. I selected C5:C11.
- Then click as follows: Home > Conditional Formatting > Highlight Cells Rules > Equal To.
Soon after, a Conditional Formatting dialog box named Equal To will open up.
- Type the name of the Region for what you are looking for duplicates. I typed UK in the Format cells that are EQUAL TO:
- Then click on the drop-down icon from the right side of the dialog box and select your desired color.
- Finally, just press OK.
Then you will get all the duplicates for the region of the UK highlighted with your selected color.
Now let’s 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:
- Later, select your desired color from the drop-down list.
- And then press OK.
Duplicates of the London region are now highlighted with a different color.
- To highlight the duplicates for the Canada region, again 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.
- Finally, just press OK.
Now, all different duplicate Regions are highlighted with different colors.
Read More: How to Highlight Duplicates in Multiple Columns in Excel (4 Ways)
Similar Readings
- How to Highlight Duplicates but Keep One in Excel (4 Methods)
- Highlight Duplicates across Multiple Worksheets in Excel (3 Formulas)
- How to Highlight Duplicates in Two Columns in Excel
2. Embed Excel VBA to Highlight Duplicates with Different Colors
Using VBA macros is quicker than the first method. Because VBA will help you to highlight all different duplicates with different colors at a time.
Steps:
- Right-click on the sheet title to open the VBA window.
- After that type the following codes in 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- Then click on the Run icon to run the codes.
Soon after, an lnputBox will pop up to select the data range.
- Select the data range C5:C11 by dragging it with your mouse.
- Later, just press OK.
Now see that 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.
Conclusion
I hope the procedures described above will be good enough to highlight duplicates in Excel with different colors. Feel free to ask any question in the comment section and please give me feedback.
Hi, thanks for the instruction. I have applied the VBA for my worksheet. I have more than 100 duplicates and the macro does not cover all the duplicate values. How can I modify the code? Thanks a lot.
Hello, DANDELION!
Please select the range properly, this macro also works for more than 100 duplicates. There is no limitation. All you need to do is after running the code select the range properly.
Good Luck!
Regards,
Sabrina Ayon
Author, ExcelDemy.
I am working with six columns separated by other data, as follows A1 and A2 merged data related is on cells b2, and b3, then it repeats for C1 and C2 merged, data related on d2 and d3, and so on. It repeats six times to the left and then six times down. The duplicate values I need to check is on the merged cells, for each duplicate value that is different use another color, if there is no duplicate no color . Any advise?
Hi Maikel,
Can you share your Excel file with us, kindly? So that we may have a look at it and give you some suggestions.
Is there a way to modify the code so that you can choose the colors?? or choose a color pallette?
Hi Anna,
Thanks for commenting. There are 13 commands under the Interior application. When you put a dot (.) after the Interior application you will find the commands. there are Color, Colorindex, Pattern, ThemeColor, etc in the command section. But all the commands have the built-in color code that’s why you won’t be able to choose a specific color using this code. but while you working with blanks you can insert the RGB command and the Custom Color Code. In our case, it is not possible as we have to maintain the same color for the duplicates. Hope, you understand our answer.
Regards,
Fahim Shahriyar Dipto
Excel & VBA Content Developer.
Is there any way to change the color scheme? The ones in this are too dark.
Hi Carrie,
Greetings. Thanks for commenting. Yes, you can change the color scheme by following the below process.
Select the data range where you want to apply Conditional Formatting. Then click as follows: Home > Conditional Formatting > Highlight Cells Rules > Equal To.
Soon after, a Conditional Formatting dialog box named Equal To will open up
Type the name of the Region for what you are looking for duplicates.
Then click on the drop-down icon from the right side of the dialog box and select Custom Format.
Therefore, the Format Cells window will appear, and select the Fill option. Then choose your desired color.
Finally, just press OK
The code is only working for the first 109 rows out of a document that contains 6000 can you point me in the right direction to get this working fully?
Hi Keaton, thanks for your query. As the code has no limitation on the number of rows, it should work in your case. Maybe the code worked for the first 109 rows only because you only selected the first 109 rows in the prompt. Kindly select the entire dataset while running the code. Hopefully, it will do the job for you. If the code still doesn’t work, you can share your file using our Exceldemy forum(https://exceldemy.com/forum/).
Hi there,
For the VBA code, cells with blank entries are also colour-coded. How may I modify the code to avoid this issue from happening?
Hi there, for the VBA code,
In my own workbook, cells with blank entries are also color-coded. Please advise on how i can modify the code so as to prevent cells with blank entries to be left with no color fill?
This code will solve your problem. The code has a condition checking values blank or not.
Output: