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.
Highlight duplicate words in a cell ignoring text case
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:
Highlight duplicate text in a cell case-sensitive
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:
How to use the macros to highlight duplicates words in Excel
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 :)
Add the code to your workbook
You start with inserting the macro's code in your Excel workbook. Here's how:
- Open the workbook where you want to highlight dupes.
- Press Alt + F11 to open the Visual Basic Editor.
- On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
- Paste the code in the Code window.
- To keep the macro for future use, be sure to save your workbook as a macro-enabled .xlsm file.
Alternatively, you can download our sample workbook and run the macro from there. The sample workbook contains the following macros:
- HighlightDupesCaseInsensitive - shades duplicates within a cell ignoring the letter case.
- HighlightDupesCaseSensitive - highlights dupes in a cell considering the letter case.
For more information, please see How to insert VBA code in Excel.
Run the macro
With the code added to you own workbook or our sample file downloaded and open, run the macro in this way:
- In your worksheet, select the cells where you wish to highlight duplicate text. This can be one range or multiple non-adjacent ranges.
- Press Alt + F8.
- Select the macro of interest and click Run.
- The macro will ask you to specify the delimiter that separates the values in the selected cells. The preset delimiter (a comma and a space in our case) will appear in the input box automatically. Depending on your needs, you can leave the default delimiter or type a different one, and then click OK.
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).
How to adjust the code for your needs
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.
Place on the same module
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!