Excel to find a word within a cell within a sentance

[Closed]
Report
Posts
1
Registration date
Thursday October 24, 2013
Status
Member
Last seen
October 24, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
2 things...

1. I need to find a word (several individual words actually) within a cell that is part of a sentence. The search will need to cover the entire column searching through every entry in that column and and it is not column A but another column like column D. The sentences can be the same and be different also.
---- Example Sentence1: The quick brown fox jumped over the lazy dog.
---- Example Sentance2: The lazy cat is upstairs on the bed.
---- Example Sentance3: Fourteen bags of wheat are needed and I don't know why.
---- The words to FIND are = fox quick dog lazy

Now, once it finds 1 or more of the words, CAN those words only be BOLDED AND COLOR CHANGED TO RED

RESULT: The quick brown fox jumped over the lazy dog.

2. Is bold the entire sentence and color/fill the entire cell yellow.


Thank You!

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
the three sentences are in A1,A2 and A3.

then try this macro

Sub test()
Dim x(1 To 4) As String, j As Integer, k As Integer
Dim r As Range, cfind As Range
Set r = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
x(1) = "fox"
x(2) = "quick"
x(3) = "dog"
x(4) = "lazy"
r.Font.Bold = False
For j = 1 To 4
Set cfind = r.Cells.Find(what:=x(j), lookat:=xlPart)
If Not cfind Is Nothing Then
k = WorksheetFunction.Search(x(j), cfind)
'MsgBox k
cfind.Characters(k, Len(x(j))).Font.Bold = True
End If
Next j

End Sub
Looks good and I understand it, trying shortly, Thank You!
OK, need a little more help. It doesn't quite work with my change to a couple parts...

The column I need to work with is D from D2 and down since D1 is the Header Row, entire column, and the column will vary in entries from day to day.

I can only get it to search down to row 6 in column D.

Also, bold is good but not enough. I need it to BOLD and RED COLOR each time.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
not clear. copy your sheest if it is not large or upload to speedyshare.com and post the download address.

reg font and red just add one more code statement
instead of
cfind.Characters(k, Len(x(j))).Font.Bold = True

try this

wih cfind.characters(k,len(x(j))).font
.bold=true
.colorindex=3
end with