VBA to bold all text that occurs between two strings.

Posted on 2009-04-30
Last Modified: 2013-11-25
I am looking for some basic VBA code to bold all text in a table column that falls between the following two strings respectively: "||" and ">>"
Please see example:
or the file attached to the question.
Thank you
Question by:PioneerSolutions
    LVL 59

    Accepted Solution

    Assuming your data in column-A from the 1st cell till the last row of A Column, Then you can use the following code which will do what you are looking for...

    Sub format()
        Dim rng As Range, r As Range, cell As Range
        Dim i As Long, x As Long, k As Long, u As Long
        Set rng = Range("a1:a" & Cells(65536, "A").End(xlUp).Row)
        For Each cell In rng
            i = Len(cell.Value)
            For x = 1 To i
                If Mid(cell.Value, x, 1) = "|" Then
                    k = x + 2
                    Do Until Mid(cell.Value, x, 1) = ">"
                        x = x + 1
                    u = x - 2
                    cell.Characters(Start:=k, Length:=u - k).Font.Bold = True
                End If
            Next x
        Next cell
    End Sub

    Open in new window

    LVL 21

    Assisted Solution

    You can do it with wildcards in a Find and Replace (i.e. no need for VBA):

    1. In the Find what box: (||)(*)(\>\>)
    2. In the Replace with box: \2
    3. While in the Replace with box, press Ctrl-B to set formatting to bold
    4. Be sure that the "Use Wildcards" search option is on (click the More... button to see the options)
    5. Click Replace All to effect the change.

    The wildcard syntax finds three expressions: the "||" followed by any characters (represented by "*") and then the ">>" string. Since a ">" is a reserved character in a wildcard expression, you need to precede it with the release character "\". When this 3-part expression is found, it gets replaced with just the second expression: the "\2" represents the 2nd of the three expressions. By setting the format to bold, the found expression is also set to bold.

    If your document is exactly as the sample you provided, you may need to include the space associated with your delimiters. For example, "(|| )(*)( \>\>)" used as the find expression will also remove the spaces after a || and before a >> string.

    Author Comment

    Dear Saurabh,
    Thank you for the code, however we are working in Word, please see the document attached to the problem. Would please kindly modify it accordingly and I will award you the answer.
    Thank you.

    Dear EricFletcher,
    I appreciate your answer, however we specifically need a vba solution as this tasks needs to be automated.
    LVL 21

    Expert Comment

    Okay, if you want to use VBA, you can use the wildcard Find and Replace within VBA: just record it and modify the code as needed. There is no reason to have to loop through the file as you do in your sample code, because the wildcard method works just fine in VBA too..
    LVL 59

    Expert Comment

    by:Saurabh Singh Teotia
    I'm not sure about how to do this in word, Since i thought its an excel question and gave you a solution accordingly.
    LVL 21

    Expert Comment

    Here is a simple VBA version of the Find and Replace I described.
    Sub SetBold()
        With Selection.Find
            .Replacement.Font.Bold = True
            .Text = "(|| )(*)( \>\>)"
            .Replacement.Text = "\2"
            .MatchWildcards = True
            .Execute Replace:=wdReplaceAll, Format:=True, Forward:=True
        End With
    End Sub

    Open in new window


    Author Comment

    Thank you for both your efforts. Neither solution works for me, however I should have been clearer in the question. I will now repost it.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
    This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now