Previous Topic Next Topic

Excel to find a word within a cell within a sentance

Closed
MeNotYou Posts 1 Registration date Thursday October 24, 2013 Status Member Last seen October 24, 2013 - Oct 24, 2013 at 05:13 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 25, 2013 at 09:48 PM
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!
Related:

4 responses

Answer 1 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 25, 2013 at 07:21 AM
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
0
Answer 2 / 4
Sir Charles
Oct 25, 2013 at 01:36 PM
Looks good and I understand it, trying shortly, Thank You!
0
Answer 3 / 4
Sir Charles
Oct 25, 2013 at 02:32 PM
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.
0
Answer 4 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 25, 2013 at 09:48 PM
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
0

Similar discussions

How to convert the tamil pdf file to tamil word file
varathalakshmi -
karthik -

12 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses