Highlighting Words Appearing Twice In Cell

[indi visual]

How can I search and highlight text in a cell that contains the same word twice?


For example if one cell contains:


The quick brown fox jumps over the fox


I would like to find a way to identify and highlight this cell somehow.


..*because it contained the word "fox" twice that is.

 

[xld]

How about a UDF

[pre]
Public Function CountDuplicates(rng As Range) As Boolean
Dim TextLen As Long
Dim WordCount As Long
Dim WordLen As Long
Dim UniqueWordCount As Long
Dim NextWord As String
Dim coll As Collection
Dim i As Long

TextLen = Len(rng.Value)
WordCount = TextLen - Len(Replace(rng.Value, " ", "")) + 1
Set coll = New Collection
On Error Resume Next
For i = 1 To TextLen

WordLen = InStr(i + 1, rng.Value & " ", " ") - i + 1
NextWord = Mid$(rng.Value & " ", i, WordLen)
coll.Add NextWord, NextWord
i = i + WordLen - 1
Next i
On Error GoTo 0
CountDuplicates = WordCount <> coll.Count
End Function
[/pre]

 

[indi visual]

I apologize xld...


But to be quite frank I know nothing about VBA.


I'm learning, and I think I at least I know that UDF means user defined function.


But I'm not sure how to use the code you provided.


How do I use it?

 

[Hui]

Copy the code

Alt F11 to goto VBA

Right Click on your workbook and Insert Code Module

paste the code into the Open Pane of the code module


In Excel use =CountDuplicates(Cell Reference)

eg: =CountDuplicates(D10)


It will return true if there is any duplicate words

 

[indi visual]

Thanks Hui.

Got it working.


But how do I tweak it to look for brackets only?


'(' and ')'

 

[Hui]

Are you looking for multiple occurences of "(" or ")" or multiple of "(text)" ?

 

[indi visual]

Brackets actually, I initially wanted text because I thought it would work the same way.


I'm looking for brackets yes.