Search Formatted Text using Excel VBA /
The following code identifies the Blue Color text and ‘tags’ them
Sub Tag_Blue_Color()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
Application.FindFormat.Font.Color = vbBlue
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.Color = vbautomatic
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.Color = vbBlue
Formatted Text in Excel (Colored)
Convert Colored Text to Tags in ExcelSearch 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 ExcelThe following code identifies the bold text and ‘tags’ them
Sub Tag_Bold()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
Application.FindFormat.Font.Bold = 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.Bold = False ' Use this if you want to remove bold
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.Bold = True
Excel with Bold Formatted Text
Excel with Tagged Text (Format) Search Formatted Text using Excel VBA /
One day a strange ‘job’ landed on my director friend M.A. Keeran. He had written a beautiful script for a film in Excel and has given for a second look. The guy who had done the second parse, underlined the parts of script that needs to be retained. Now we need to extract those ranges that have underlines. The following code is the modification/extension of that: it identifies the underlined text and ‘tags’ them
Sub Tag_UnderLine()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
Application.FindFormat.Font.Underline = XlUnderlineStyle.xlUnderlineStyleSingle
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.Underline = XlUnderlineStyle.xlUnderlineStyleNone ' Use this if you want to remove underline in first column
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.Underline = XlUnderlineStyle.xlUnderlineStyleSingle
Excel with Formatted Text (Underline)
Excel with TagsProgrammatically uninstall Excel Addins using VBA
Sub UnInstall_Addins_From_EXcel_AddinsList()
Dim oXLAddin As AddIn
For Each oXLAddin In Application.AddIns
Debug.Print oXLAddin.FullName
If oXLAddin.Installed = True Then
oXLAddin.Installed = False
End If
Next oXLAddin
End Sub
See also:
Call a Method in VSTO Addin from Visual Basic Applications
Programmatically Delete Word Addins from the Addins List using VBA
UnInstall Word Addins using VBA
Posts
All Comments
