by Svetlana Cheusheva, updated on
The tutorial shows how to highlight duplicate words or text strings within a cell using VBA.
Excel Conditional Formatting makes it possible to highlight duplicates in every possible way you can think of: with or without 1st occurrences, in a single column or multiple columns, consecutive duplicate cells, and entire rows based on identical values in a key column. But, as usual, there is a "but". Conditional formatting rules work on a cell level while you may want to highlight duplicate text rather than entire cells. This can only be done with macros. Even if you don't have any experience with VBA, please don't rush to close this page. Here, you will find ready-to-use code examples and the detailed instructions on how to use them in your worksheets.
This example shows how to shade duplicate words or text strings within a cell in red font color like shown in the image below. Please notice that lowercase and uppercase letters are treated as the same characters. For example, orange, ORANGE and Orange are deemed to be the same word.
The macro's code is as follows:
In most situations, we tend to ignore the letter case when working with text entries in Excel. Under certain circumstances, however, the text case does matter. For instance, if you are dealing with IDs, passwords, or other records of that kind, the strings such as 1-AA, 1-aa and 1-Aa are not duplicates and should not be highlighted:
In this case, use the following version of the code:
If you are a beginner in using VBA, the below step-by-step instructions will comfortably walk you through. Experienced users may just pick the download link and skip the rest :)
You start with inserting the macro's code in your Excel workbook. Here's how:
Alternatively, you can download our sample workbook and run the macro from there. The sample workbook contains the following macros:
For more information, please see How to insert VBA code in Excel.
With the code added to you own workbook or our sample file downloaded and open, run the macro in this way:
A moment later, all duplicate strings in the selected cells will be shaded in red color (or whatever font color is set in your code).
Tip. To quickly remove duplicates within a cell, you can utilize Remove Duplicate Substrings, one of many timesaving tools included in our Ultimate Suite.
With these usage notes and the very basic knowledge of VBA (or just closely following the below instructions), you can easily modify the codes in exact accordance with your needs.
As you may notice, both macros (HighlightDupesCaseSensitive and HighlightDupesCaseInsensitive) call the HighlightDupeWordsInCell function. The difference between the two macros above is only in the 3rd parameter (CaseSensitive) passed to the said function.
For case-sensitive search, it is set to TRUE:
Call HighlightDupeWordsInCell(Cell, Delimiter, True)
For case-insensitive search, it is set to FALSE:
Call HighlightDupeWordsInCell(Cell, Delimiter, False)
For the macros to work, the code of the HighlightDupeWordsInCell function must be placed onto the same module as the macros.
When run, the macro will ask you to specify the delimiter that separates words/strings in the selected cells. The default delimiter is a comma and a space (", ") and it is preset in the InputBox:
Delimiter = InputBox("Specify the delimiter that separates values in a cell", "Delimiter", ", ")
In your code, you are free to use any other character(s) as the predefined delimiter.
By default, the HighlightDupeWordsInCell function shades duplicates in red font color. The color is defined in this line:
Cell.Characters(positionInText, Len(word)).Font.Color = vbRed
Here, vbRed is a sort of VBA color constant. To display dupes in a different color, you can replace vbRed with another constant such as vbGreen, vbYellow, vbBlue, etc. The list of supported color constants can be found here.
That's how to highlight duplicate words in Excel cells. I thank you for reading and hope to see you on our blog next week!
Code examples to highlight duplicates in a cell (.xlsm file)
Ultimate Suite 14-day fully-functional version (.exe file)
Table of contents