Using a macro to create conditional formatting in Word as per Excel

Question

I have an Excel doc that has conditional formatting that changes the background colour of a cell, dependant upon the specific [dropdown] text that is selected. E.g Yes, changes cell background to Green, No to Red, Unknown to Yellow and Not applicable to Grey.

All easy stuff.

I then need to mailmerge to a Word doc to populate the Word doc with the Excel table - the Word doc also has other non-excel related text.

As conditional formatting of the cells does not come across, I have used the under noted code in a macro to change the background colour in Word. It runs, but seems after the first loop, it seems to crash with the error - Runtime error 5907 - there is no table at this location.

the line in the code r.Cells(1).Shading.BackgroundPatternColorIndex = backgroundColor is highlighted yellow.

My level of coding is basic so I don't know what's going wrong.

If anyone is able to offer an insight to a simple solution, I would appreciate it.

Thank

Dim r As Range

Sub UBC()
    color "No", wdRed
    color "Yes", wdGreen
    color "Unknown", wdYellow
    color "Not Applicable", wdGray50
End Sub

Function color(text As String, backgroundColor As WdColorIndex)
    Set r = ActiveDocument.Range

    With r.Find
       Do While .Execute(FindText:=text, MatchWholeWord:=True, Forward:=True) = True
    r.Cells(1).Shading.BackgroundPatternColorIndex = backgroundColor
       Loop
    End With
End Function

Cindy Meister Cindy Meister · Accepted Answer · 2018-09-06T07:07:45

It's possible (likely) that Word is finding a character combination outside a table cell. Safest is to test whether the found term is actually in a table. (Note: I've also put the variable declaration Dim r inside the Function...)

Sub UBC()
    color "No", wdRed
    color "Yes", wdGreen
    color "Unknown", wdYellow
    color "Not Applicable", wdGray50
End Sub

Function color(text As String, backgroundColor As WdColorIndex)
    Dim r As Word.Range

    Set r = ActiveDocument.content

    With r.Find
       Do While .Execute(findText:=text, MatchWholeWord:=True, Forward:=True) = True
          If r.Tables.Count > 0 Then
            r.Cells(1).Shading.BackgroundPatternColorIndex = backgroundColor
          End If
       Loop
    End With
End Function

Using a macro to create conditional formatting in Word as per Excel

1 Answers