Apply formatting to only a portion of a cell's contents

[Samantha]

I have a 150 page worksheet that contains the information on clients for which our law firm has done estate planning dating back to 1952. A few years ago, I migrated dozens of Word file indices to one Excel master index, and I am now trying to clean up that 'new' document.

As you might expect, many the clients for whom we have prepared Last Will and Testaments are now deceased, and that fact needs to be noted as a parenthetical (Dec'd) next to their name in the index, and I want to bold and italicize that notation, as well as have it appear in a slightly smaller font size.

I know that direct formatting can be applied to a portion of a cell's contents, simply by selecting the relevant portion and manually changing the formating, but I'd like to be able to do a global search and replace to find the string (Dec'd) and apply the formatting to that portion of the cell contents only. Unfortunately, the results of the Search & Replace affected the entire contents of the cells. Am I missing a setting in the search & replace dialogue to have only the "(Dec'd)" portion of the cell affected by the formatting change?

[StuartR]

You haven't missed anything. This is a very annoying feature of Excel.

[Samantha]

You haven't missed anything. This is a very annoying feature of Excel.

Thanks for the quick confirmation Stuart, even though it's not the answer I was hoping for.

[StuartR]

If you have to do this often, or the spreadsheet is big enough to justify the effort, then you could write a Macro to do this.

[mbarron]

This macro checks all entries in column one and if it finds (Dec'd) in the cell, it changes the font to bold, italic and font size of 8.

Code: Select all

Sub deceased()
Dim i As Long, strD As String, iDec As Integer
i = 1
Do Until Cells(i, 1) = ""
If InStr(Cells(i, 1), "(Dec'd)") > 1 Then
iDec = InStr(Cells(i, 1), "(Dec'd)")

With Cells(i, 1).Characters(Start:=iDec, Length:=7).Font
        .Bold = True
        .Italic = True
        .Size = 8
End With


End If
i = i + 1
Loop

End Sub

[Samantha]

This macro checks all entries in column one and if it finds (Dec'd) in the cell, it changes the font to bold, italic and font size of 8.

Code: Select all

Sub deceased()
Dim i As Long, strD As String, iDec As Integer
i = 1
Do Until Cells(i, 1) = ""
If InStr(Cells(i, 1), "(Dec'd)") > 1 Then
iDec = InStr(Cells(i, 1), "(Dec'd)")

With Cells(i, 1).Characters(Start:=iDec, Length:=7).Font
        .Bold = True
        .Italic = True
        .Size = 8
End With


End If
i = i + 1
Loop

End Sub

That worked wonderfully. Thank you sooo much!

Sorry it's taken so long to report back -- this is one of those projects that I only attend to when nothing else is on my desk, which rarely happens. But I couldn't let your assistance go unappreciated any longer, so I added your macro to my spreadsheet before closing up shop for the day, and it ran beautifully.