Determine if 2 cells share a common word

  • Thread starter Thread starter paul c
  • Start date Start date
P

paul c

I have a list with two text columns. I want to identify the rows where the
cells share a common word. For example,
Fujitsu Consulting in col A and Software Engineer in col B would not share a
common word but
Starbucks in col A and Starbucks Barista in col B would share a common word.
 
Sub Highlight_Word()
Dim rng As Range
Dim Cell As Range
Dim start_str As Integer


myword = InputBox("Enter the search string ")

Mylen = Len(myword)
Set rng = Selection
For Each Cell In rng
' start_str = InStr(cell.Value, myword) '(case sensitive)
start_str = InStr(1, Cell.Value, myword, vbTextCompare) '(non case
sensivive)

If start_str Then
Cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Next
End Sub

Select Col A and Col B, then run the macro.
 
Hi,

Let's say the entry is in A4:B4 and then goes downwards. Click on cell A4
and go to Conditional formatting. Write the following formula in
conditional formatting

=OR(ISNUMBER(SEARCH($A4,$B4)),ISNUMBER(SEARCH($B4,$A4)))

Select a format colour and click on OK. This will highlight all those
records (both columns), where the entry is found in the other column.

However please note that if one column has starbuck and the other has
starbucks, it will highlight both entries.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top