How to Search a specific Colored Text (Range) using Excel VBA

Search Formatted Text using Excel VBA / Extract Colored Range using Excel VBA / Excel VBA Tag Color Text

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 = "" & 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 Excel

Convert Formatted Text to Tags in Excel, Tag formatted text in Excel

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

How to Search Bold Text (Range) using Excel VBA

Search Formatted Text using Excel VBA / Extract Boldfaced Range using Excel VBA / Excel VBA Tag Bold Text

The 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)

How to Search Underlined Text (Range) using Excel VBA

Search Formatted Text using Excel VBA / Extract Underlined Range using Excel VBA / Excel VBA Tag Underlined Text

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 Tags