Prev: #NUM!
Next: linking a range of cells (=) to another range
From: paul c on 13 Jan 2010 15:59
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.


From: ryguy7272 on 13 Jan 2010 16:19

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.
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"paul c" wrote:

> 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.
>
>
From: Ashish Mathur on 13 Jan 2010 22:52
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

"paul c" <paulc(a)discussions.microsoft.com> wrote in message
news:5F328146-BAC9-44C3-8195-6D5C0680EDFB(a)microsoft.com...
> 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.
>
>
 | 
Pages: 1
Prev: #NUM!
Next: linking a range of cells (=) to another range