• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1505
  • Last Modified:

VBA to bold all text that occurs between two strings.

Hi,
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:
http://www.eligreen.com/share/Bold_String_Pattern.pdf
or the file attached to the question.
Thank you
eg
Bold-String-Pattern.doc
0
PioneerSolutions
Asked:
PioneerSolutions
  • 3
  • 2
  • 2
2 Solutions
 
Saurabh Singh TeotiaCommented:
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...
Saurabh...

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
                Loop
                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

0
 
Eric FletcherCommented:
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.
0
 
PioneerSolutionsAuthor Commented:
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.
eg

Dear EricFletcher,
I appreciate your answer, however we specifically need a vba solution as this tasks needs to be automated.
Regards
eg
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Eric FletcherCommented:
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..
0
 
Saurabh Singh TeotiaCommented:
I'm not sure about how to do this in word, Since i thought its an excel question and gave you a solution accordingly.
Saurabh...
0
 
Eric FletcherCommented:
Here is a simple VBA version of the Find and Replace I described.
Sub SetBold()
    With Selection.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .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

0
 
PioneerSolutionsAuthor Commented:
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.
eg
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now