CODEWITHSUNDEEP
regexvbams-word
user2576682
user2576682

Reputation: 123

Word VBA use Regex for formatting change

I have a word document that has SQL code in it. I have great code that finds specific terms I didn't realize that I had so many different terms in my document. I stopped at over 140 terms. I tried to add regex but I did it wrong. Would it be possible to have it find the start word SELECT and the end word FROM and change the background color to wdBrightGreen for everything between those terms? I did not put all the fields in I stopped at three. Is there a way to put the ArrFind to search the whole document instead of specific terms?

Dim ArrFnd As Variant
Dim i As Long
Dim x As Long
Dim y As Long
Dim regexObject As RegExp

Dim strField1(1 To 200) As String
Dim strField2(1 To 20) As String
strField1(1) = "SELECT DISTINCT    policy_id"
strField1(2) = "SELECT DISTINCT  policy_id"
strField1(3) = "SELECT DISTINCT   P.policy_id"

For x = 1 To 150
    ArrFnd = Array(strField1(x))
    With ActiveDocument
        For i = 0 To UBound(ArrFnd)
            With .Range
                With .Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .Format = False
                .Forward = True
                .Wrap = wdFindStop
                .MatchCase = True
                .MatchWholeWord = True
                        .Text = ArrFnd(i)
                    With regexObject
                         .Pattern = "[SELECT-FROM]"
                    End With
                .Replacement.Text = ""
            End With
                Do While .Find.Execute
                    .Font.Shading.BackgroundPatternColorIndex = wdBrightGreen
                    .Collapse wdCollapseEnd
                    Loop
            End With
        Next i
    End With
Next x

Upvotes: 0

Views: 248

Answers (1)

macropod
macropod

Reputation: 13515

One wonders why you persist with the approach taken in your code, in light of: MS Word VBA Can't select specific words to change background color

Regardless, for what you are now describing, all you need is:

Sub HiliteWords()
Application.ScreenUpdating = False
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Format = False
    .Forward = True
    .Wrap = wdFindStop
    .MatchWildcards = True
    .Text = "SELECT[!^13^l]@FROM"
    .Replacement.Text = ""
  End With
  Do While .Find.Execute
    .Start = .Words.First.End
    .End = .Words.Last.Start - 1
    .Font.Shading.BackgroundPatternColorIndex = wdBrightGreen
    .Collapse wdCollapseEnd
  Loop
End With
Application.ScreenUpdating = True
End Sub

If you also don't want DISTINCT shaded, insert:

If .Words.First.Text = "DISTINCT " Then .Start = .Words.First.End

before:

    .End = .Words.Last.Start - 1

Upvotes: 1

Related Questions