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