Tuesday, December 16, 2008

How to Search Italic Text (Range) using Excel VBA

Search Formatted Text using Excel VBA / Extract Italicized Range using Excel VBA / Excel VBA Tag Italic Text

The following code identifies the Italic text and ‘tags’ them

Sub Tag_Italic()

Dim oWS As Worksheet

Dim oRng As Range

Dim FirstUL

Set oWS = ActiveSheet

Application.FindFormat.Clear

Application.FindFormat.Font.Italic = True

Set oRng = oWS.Range("A1:A1000").Find(What:="", LookIn:=xlValues, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=True)

If Not oRng Is Nothing Then

FirstUL = oRng.Row

Do

oRng.Font.Italic = False ' Use this if you want to remove italics

oRng.Value2 = "" & oRng.Value2 & ""

Set oRng = oWS.Range("A" & CStr(oRng.Row + 1) & ":A1000").Find(What:="", LookIn:=xlValues, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=True)

Loop While Not oRng Is Nothing

End If

End Sub

In the above code we have used

Application.FindFormat.Clear

Clears the criterias set in the FindFormat property and then set the format to find using

Application.FindFormat.Font.Italic = True


Italic Formatted Text in Excel
Formatted Text replaced by Tags in Excel

3 comments:

  1. Anonymous12:14 PM

    it does not add the tags when I run this macro. It just turns the italics into regular font.

    ReplyDelete
  2. Toby Scamell4:29 AM

    The second line after the "Do" statement:

    "oRng.Value2 = "" & oRng.Value2 & """

    Should read:

    oRng.Value2 = "" & oRng.Value2 & ""

    However, the above code will not modify cells which have mixed formatting.

    ReplyDelete
  3. Toby tried to answer, I guess, but had the same problem as the post. The "" value isn't really what he is trying to say - the italics tag should go there, but is being stripped out before it gets posted here.

    I'll try again using html entities to see if it will come through:

    "oRng.Value2 = "<i>" & oRng.Value2 & "</i>"

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.