Forum Discussion
Find and replace with replacements in italics
You need a VBA macro for this in Excel.
Sub Italicize() Const strText = "London: Penguin Books" Dim rng As Range Dim adr As String Dim pos As Long Set rng = Selection.Find(What:=strText, LookAt:=xlPart) If Not rng Is Nothing Then Application.ScreenUpdating = False adr = rng.Address Do pos = InStr(rng.Value, strText) rng.Characters(Start:=pos, Length:=Len(strText)).Font.Italic = True Set rng = Selection.Find(What:=strText, After:=rng, LookAt:=xlPart) If rng Is Nothing Then Exit Do Loop Until rng.Address = adr Application.ScreenUpdating = True End If End Sub
How can we determine exactly which part of the cell contents should be italicized?
As described in my original post. The cell contains:
ORWELL, (George). - Collected Essays and Journalism. Vol. 1. London: Penguin Books, 1973.
The words 'London: Penguin Books' need to be in italics. There are many cells in the spreadsheet, each containing an entry for a book, as described above. Many of the books were published at 'London: Penguin Books' and those three words [and the colon of course] need italics.
Using the Find and Replace utility, I enter 'London: Penguin Books' [without the single quotes], and set it it up so that the specified text string is changed to italics. After the operation is complete the entire contents of each cell containing that string is italicized, not just the three words. Easy to do in Word, but apparently rather too sophisticated in Excel.
You need a VBA macro for this in Excel.
Sub Italicize() Const strText = "London: Penguin Books" Dim rng As Range Dim adr As String Dim pos As Long Set rng = Selection.Find(What:=strText, LookAt:=xlPart) If Not rng Is Nothing Then Application.ScreenUpdating = False adr = rng.Address Do pos = InStr(rng.Value, strText) rng.Characters(Start:=pos, Length:=Len(strText)).Font.Italic = True Set rng = Selection.Find(What:=strText, After:=rng, LookAt:=xlPart) If rng Is Nothing Then Exit Do Loop Until rng.Address = adr Application.ScreenUpdating = True End If End SubNice! Thank you so much, Hans. A most elegant solution.
