Finding string and color the found string

[Agustus]

Hi all,

I tried to find a string in selected cells with the following code with
no success:

Sub Findstr()
Dim myStr As String

myStr = InputBox("Enter word to be searched")
If myStr = "" Then Exit Sub

With Selection.Characters(Start:=InStr(1, ActiveCell.Value, myStr), _
Length:=Len(myStr)).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
End Sub

Any advice on improving the code is much appreciated.

TIA.

 

[Tom Ogilvy]

It worked for me if the string was in the activecell and only one cell was
selected and the substring matched exactly (including case). If not, it
worked on the first n characters in the string where n was the length of the
string being searched for.


Sub Findstr()
Dim myStr As String
Dim iloc as Long
myStr = InputBox("Enter word to be searched")
If myStr = "" Then Exit Sub

activeCell.Select
iloc = Instr(1,ActiveCell.Value,myStr,vbTextCompare)
if iloc <> 0 then
With Selection.Characters(Start:=iloc, _
Length:=Len(myStr)).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
Else
msgbox mystr & " was not found"
End if
End Sub