change font color on search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I need to do a search and replace in a worksheet. When I match a string,
I want to change the color.

For example, if the string is
"Hello everyone!"

I want to search for any word that starts and ends with 'e', then change the
word color to be blue.

Is this possible with a cell formula? Do I need a sheet macro? I cant seem
to figure it out with either!

Thanks in advance for any help! (Office 2003)
 
What you are asking cannot be done with formulas. Neither with
conditional formatting, because you want formatting at text and not
cell level.

The following marco will highlight the word srch in all cells in the
range C1:D10 of Sheet1:

Sub Highlight(srch As String)
Dim dta As Range, c As Range

Set dta = Sheets("Sheet1").Range("C1:D10")
For Each c In dta
If InStr(1, c.Value, srch) Then
With c.Characters(Start:=InStr(1, c.Value, srch),
Length:=Len(srch)).Font
.ColorIndex = 41
End With
End If
Next c
End Sub

HTH
Kostis Vezerides
 
Back
Top