Search Formatted Text using Excel VBA /
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
Posts
it does not add the tags when I run this macro. It just turns the italics into regular font.
ReplyDeleteThe second line after the "Do" statement:
ReplyDelete"oRng.Value2 = "" & oRng.Value2 & """
Should read:
oRng.Value2 = "" & oRng.Value2 & ""
However, the above code will not modify cells which have mixed formatting.
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.
ReplyDeleteI'll try again using html entities to see if it will come through:
"oRng.Value2 = "<i>" & oRng.Value2 & "</i>"