Searching for text within string and changing colour of found text

[rjf298]

Hi there - I have some code that allows me to search for pre-defined text (eg. house) in a range and then format the resultant text in the string (coloured red).

What I want to know is how to change the code so that rather than searching for set text it searches for whatever text is contained within a certain cell (ie. user-defined)?

The code I have is:

Sub HighLight()
Dim WS As Worksheet, c As Range
Dim FindWord As String
Dim MyStart As Long, MyLenght As Long

FindWord = "house"

For Each WS In Worksheets
With WS.Cells
Set c = .Find(FindWord, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MyStart = InStr(c.Value, FindWord)
MyLength = Len(FindWord)
With c.Characters(Start:=MyStart, Length:=MyLength).Font
.Size = 10
.Color = -16776961
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next

End Sub

 

[Andrew Poulsom]

Maybe:

FindWord = Worksheets("Sheet1").Range("A1").value

Change the worksheet and range references to suit.

 

[rjf298]

Thanks very much for that.

Im new to VBA so am adapting a colleagues code - is there any reason why when entering a search term of x characters, the code only highlights the first x characters of the cell it has found my search term in, and not the actual word itself?!

Thanks