Formatting table cells in a document

lukestkd · #1 02-24-2014, 06:13 AM

Hello all,
I have the following code which formats a specific piece of text in a mail merge document, however the text is in a series of cells, and I would like to be able to format the actual cell rather than the text itself.

Is there a way to modify the code I have to do this?

Many thanks,

Luke

Code:

Sub HighlightTargetsN()

Dim range As range
Dim i As Long
Dim TargetList

TargetList = Array("MMN") ' put list of terms to find here

For i = 0 To UBound(TargetList)

Set range = ActiveDocument.range

With range.Find
.Text = TargetList(i)
.Format = True
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False

Do While .Execute(Forward:=True) = True
range.HighlightColorIndex = wdRed
range.Bold = True
range.Font.ColorIndex = wdRed
range.Font.Underline = wdUnderlineThick
range.Font.UnderlineColor = wdRed
range.Font.Name = "TW Cen MT"
range.Font.Size = 14
Loop

End With
Next

End Sub

macropod · #2 02-24-2014, 02:21 PM

You could use code like:

Code:

Sub HighlightTargetsN()
Dim Rng As Range, i As Long, TargetList
TargetList = Array("MMN") ' put list of terms to find here
For i = 0 To UBound(TargetList)
  Set Rng = ActiveDocument.Range
  With Rng
    With .Find
      .Text = TargetList(i)
      .Format = True
      .MatchCase = True
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Forward = True
      .Wrap = wdFindStop
      .Execute
    End With
    Do While .Find.Found
      .HighlightColorIndex = wdBrightGreen
      With .Font
        .Bold = True
        .ColorIndex = wdRed
        .Underline = wdUnderlineThick
        .UnderlineColor = wdRed
        .Name = "TW Cen MT"
        .Size = 14
      End With
      If .Information(wdWithInTable) = True Then
        .Cells(1).Shading.BackgroundPatternColorIndex = wdYellow
      End If
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
Next
End Sub

Note: I've played around with the colouring so as to differentiate each component's actions. Also, it's not a good idea to give variables the same names as reserved words (e.g. range), as this can lead to strange errors.

For a different way of handling this, which uses the mailmerge functionality instead of post-processing via a macro, see: http://answers.microsoft.com/en-us/o...7-86553a7f8188

macropod · #3 02-24-2014, 02:37 PM

Cross-posted at: http://www.excelforum.com/newreply.p...6843&noquote=1 and http://www.vbaexpress.com/forum/show...-in-a-document
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA and hyperlinks in table cells	viuf	PowerPoint	13	02-19-2012 10:34 AM
Make Cells Mimic The Formatting Of One Cell	skoz55	Excel	1	11-09-2011 09:00 AM
Copy table cell formatting across multiple cells / tables	pakistanray	Word Tables	2	10-31-2011 08:07 AM
Formatting Cells for Currency and Accounting	Sparetyme	Excel	1	07-11-2010 01:26 PM
Cut and paste a range of cells and preserve formatting	StarWeaver	Excel	1	03-02-2010 01:41 PM