Search and replace macro

[timecop]

Hello,
I'm writing a macro similar to the search and replace function of excel. What I'm trying to do is:
a. Search the entire worksheet for a specific word.
b. If found, replace that specific text (in that particular cell) with different font color.

Using the search and replace functions of excel, when I change the format of the "replace with", upon Replace, the entire cell format was change. I only need to change the specific text the I've searched.
Could someone help me get this working. Thanks.

[colofnature]

This

Sub change_to_colour(in_range As Range, what As Variant, clr As Long)
    
    With in_range
        Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Characters(Start:=InStr(1, ucase(c.Value), ucase(what)), Length:=Len(what)).Font.ColorIndex = clr
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

End Sub

will search through all the cells in "in_range" for the string called "what" and change it to colorindex clr. Try it out with

Sub test()
' change "word" to whatever you're trying to highlight
    change_to_colour ActiveSheet.UsedRange, "word", 3
End Sub

Col


[edit] Changed code to use UCase [/edit]

[timecop]

Thanks for a quick a reply. But what if I want to change the search text with different text? I also noticed that if there are 2 or 3 search words in the same cell, only the first occurrence was change to red fonts. would it be possible to change all of them?

[colofnature]

This will change all instances of the word in any cell

Sub change_to_colour(in_range As Range, what As Variant, clr As Long)
    
    With in_range
        Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Characters(Start:=InStr(1, UCase(c.Value), UCase(what)), Length:=Len(what)).Font.ColorIndex = clr
                foundText = InStr(1, UCase(c.Value), UCase(what))
                Do While foundText <> 0
                    c.Characters(Start:=InStr(foundText, UCase(c.Value), UCase(what)), Length:=Len(what)).Font.ColorIndex = clr
                    foundText = InStr(foundText + Len(what), UCase(c.Value), UCase(what))
                Loop
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

End Sub

To substitute the string you're searching for with a different string use a normal Replace command.


Col

[timecop]

hello,
I used the replace command but when I replace 2 words in same cell, for example, I first replace "after market" with "aftermarket" then "amongst" with "among", the format of the first word replace was reset and only the second word was change to red fonts. It seems that there's a problem with the Replace command, do you have any idea about this?