Tuesday, December 16, 2008

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)

5 comments:

  1. Anonymous5:30 AM

    This is not working error Set Rng what is "o"?

    Sorry I m not Expert
    explain in detail

    ReplyDelete
  2. We are assigning a Worksheet Range A1:A1000 to the Range object

    Can you try

    Set oRng = oWS.Range("A1:A1000").Find(What:="", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=True)

    The above should be in single line

    ReplyDelete
  3. Anonymous8:14 PM

    This worked for me:

    Sub Tag_Bold()
    For Each c In [A1:A10]
    If c.Font.Bold = True Then
    c.Value2 = "-" & c.Value2 & "-"
    End If
    Next
    End Sub

    seems quite a bit faster...

    ReplyDelete
  4. Hi Anonymous
    Your Macro Code Very Useful Us Thanks For Your Valuable Code
    If Any New Easy Code is there Pls Sent me
    I also a Beginners in Excel Macro i want to Your Help
    If you Dont mine pls Visit My Blog www.amudhaExceltips.blogspot.com
    Keep in touch Always
    Thanks

    ReplyDelete
  5. Anonymous12:06 PM

    how do i do the same thinig but i would need to find specifice bold values within a cell...

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group