Highlight Matching Text in different cells

[megnarium]

Hi All


Here is the code Im currently using:




Private Sub Worksheet_SelectionChange(ByVal rngSel As Range)
Set rngTable = [A2:E100]
If rngSel.Count > 1 Or Intersect(rngSel, rngTable) Is Nothing Then End
For Each rngCell In rngTable
rngCell.Interior.ColorIndex = IIf(rngCell.Value = rngSel.Value, 33, -4142)
Next
End Sub


This works very well EXCEPT if I have more than one text value in the same cell. I have attached the worksheet.


The way I get more than one set of words in the cell is by the following:


*Supplier* 'option enter'
*supplier* 'option enter' etc. This way I have more than one set of words in a cell.


Suggestions? I am wondering if I can highlight by Matching text not matching cells. Just my thoughts.

[p45cal]

try:
Private Sub Worksheet_SelectionChange(ByVal rngSel As Range)
Set rngTable = [A2:E100]
If rngSel.Count > 1 Or Intersect(rngSel, rngTable) Is Nothing Then End
Words = Split(rngSel.Value)
For Each rngCell In rngTable
rngCell.Interior.ColorIndex = xlNone
mywords = Split(rngCell.Value)
If UBound(mywords) > -1 Then
For Each word In Words
x = Application.Match(word, mywords, 0)
If Not IsError(x) Then
rngCell.Interior.ColorIndex = 33
Exit For
End If
Next word
End If
Next rngCell
End Sub

[megnarium]

Awesome that totally worked!!

Thank you :)

[snb]

That's what conditional formatting is desgned for.