- Excel highlight duplicates how to#
- Excel highlight duplicates code#
- Excel highlight duplicates download#
Excel highlight duplicates code#
With the code added to you own workbook or our sample file downloaded and open, run the macro in this way:
Excel highlight duplicates how to#
HighlightDupesCaseSensitive - highlights dupes in a cell considering the letter case.įor more information, please see How to insert VBA code in Excel. HighlightDupesCaseInsensitive - shades duplicates within a cell ignoring the letter case. 
The sample workbook contains the following macros:
Excel highlight duplicates download#
xlsm file.Īlternatively, you can download our sample workbook and run the macro from there.
To keep the macro for future use, be sure to save your workbook as a macro-enabled. On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu. 
Press Alt + F11 to open the Visual Basic Editor. Open the workbook where you want to highlight dupes. You start with inserting the macro's code in your Excel workbook. Experienced users may just pick the download link and skip the rest :) Add the code to your workbook If you are a beginner in using VBA, the below step-by-step instructions will comfortably walk you through. In this case, use the following version of the code:Ĭall HighlightDupeWordsInCell(Cell, Delimiter, True)Įnd Sub How to use the macros to highlight duplicates words in Excel 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: Under certain circumstances, however, the text case does matter. In most situations, we tend to ignore the letter case when working with text entries in Excel. Text = "" For Index = LBound(words) To UBound(words)Ĭell.Characters(Len(text) - Len(word) + 1, Len(word)).Font.Color = vbRedĮnd Sub Highlight duplicate text in a cell case-sensitive Words = Split(LCase(Cell.Value), Delimiter)Įnd If For wordIndex = LBound(words) To UBound(words) - 1įor nextWordIndex = wordIndex + 1 To UBound(words) Next End Sub Sub HighlightDupeWordsInCell(Cell As Range, Optional Delimiter As String = " ", Optional CaseSensitive As Boolean = True)ĭim text As String Dim words() As String Dim word As String Dim wordIndex, matchCount, positionInText As Integer If CaseSensitive Then Public Sub HighlightDupesCaseInsensitive()ĭim Delimiter As StringDelimiter = InputBox( "Enter the delimiter that separates values in a cell", "Delimiter", ", ") For Each Cell In Application.SelectionĬall HighlightDupeWordsInCell(Cell, Delimiter, False) For example, orange, ORANGE and Orange are deemed to be the same word. Please notice that lowercase and uppercase letters are treated as the same characters. This example shows how to shade duplicate words or text strings within a cell in red font color like shown in the image below. Highlight duplicate words in a cell ignoring text case How to use the macros to highlight duplicates text/words in Excel. Shade duplicates within a cell (case-sensitive). Highlight duplicates in a cell (case-insensitive). Here, you will find ready-to-use code examples and the detailed instructions on how to use them in your worksheets. 
Even if you don't have any experience with VBA, please don't rush to close this page. Conditional formatting rules work on a cell level while you may want to highlight duplicate text rather than entire cells.
The tutorial shows how to highlight duplicate words or text strings within a cell using VBA.Įxcel Conditional Formatting makes it possible to highlight duplicates in every possible way you can think of: with or without 1 st occurrences, in a single column or multiple columns, consecutive duplicate cells, and entire rows based on identical values in a key column.
0 kommentar(er)
