Trying to find text and replace it with the same text only in italics in a worksheet

[Dianne Reed]

Hello all,
I am trying to find specific text throughout an excel document (2007) and replace it with the same text but in italics. I tried using the options/format function and selecting italics for the "replace" text but it replaces the entire cell text in italics instead. Can someone offer some assistance?

Thank you,
Dianne

[ragulduy]

I'm not aware of a method to do this without using a macro, although there might be one. a VBA solution would be something like:

Sub macro_1()
Dim rng, first_rng, search_term, ws
search_term = "example search text" 'change as appropriate
For Each ws In ActiveWorkbook.Sheets
    Set rng = ws.Cells.Find(search_term)
    Set first_rng = rng
    Do
        rng.Characters(InStr(1, rng, search_term), Len(search_term)).Font.Italic = True
    Set rng = ActiveSheet.Cells.FindNext(rng)
    Loop Until first_rng.Address = rng.Address
Next
End Sub

You need to press alt+f11 to bring up the vba editor and then copy and paste the code into a new module and run it (F5).

[Dianne Reed]

I copied this code into the vba editor and received the following error message when trying to run it:

Run-time error '91':

Object variable or With block variable not set

It highlights the following line of code:

rng.Characters(InStr(1, rng, search_term), Len(search_term)).Font.Italic = True

Can you help?

Thanks,
Dianne

[ragulduy]

It worked ok for me when I tested it, you might get that error if the search term doesn't exist? Maybe include an error handling for that:
Sub macro_1()
Dim rng, first_rng, search_term, ws
search_term = "example search text" 'change as appropriate
For Each ws In ActiveWorkbook.Sheets
Set rng = ws.Cells.Find(search_term)
Set first_rng = rng
if not rng is nothing
Do
rng.Characters(InStr(1, rng, search_term), Len(search_term)).Font.Italic = True
Set rng = ActiveSheet.Cells.FindNext(rng)
Loop Until first_rng.Address = rng.Address
end if
Next
End Sub

[Dianne Reed]

I replaced the original code with the new code that you sent and received a syntax error with the following line highlighted:

if not rng is nothing

I also double-checked and there are several instances of the search term throughout the document.

Any other ideas?

Thank you,
Dianne

[ragulduy]

Sorry I don't have any other ideas, I tested it on my machine and it seemed to work ok.