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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |