Microsoft Office Forums Colour code mail merge header table cell backgrounds

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-12-2014, 08:09 AM
ScotsMaverick ScotsMaverick is offline Colour code mail merge header table cell backgrounds Windows 7 64bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Novice
Colour code mail merge header table cell backgrounds
 
Join Date: Jun 2014
Posts: 2
ScotsMaverick is on a distinguished road
Default Colour code mail merge header table cell backgrounds

Hi,

So here's the problem that's been challenging me the last few hours. I've recently been creating a mail merged document which I had hoped to have generating different coloured headers for each mail merged page depending on a mail merged field in the header table called "Category".

The goal is to change this cell's background colour on every page depending on the text that has been mail merged into the cell. I've previously done a bit of amateur VBA using excel, but the difficulties of selecting post-mail merged header cells using bookmarks in word are defeating me.



It's rough but here is the code skeleton

Code:
Sub ColourCode()

Dim Cat1 As String
Dim Cat2 As String
Dim Cat3 As String
Dim Cat4 As String
Dim Cat5 As String
Dim X As Integer
Dim Ctext As String

Cat1 = "Category: 1 - Easy fix"
Cat2 = "Category: 2 - Instrument tubing fix"
Cat3 = "Category: 3 - Further assessment"
Cat4 = "Category: 4 - Clamp"
Cat5 = "Category: 5 - Engineering"

Documents.Open FileName:="C:\Users\me\Documents\Letters1.docm"

X = ActiveDocument.Pages.Count

For Each ActiveDocument.Tables In ActiveDocument

If ActiveDocument.Bookmarks("Category").Range.Text = Cat1 Then
    Bookmarks("Category").Range.Shading.BackgroundPatternColorIndex = wdBlue
ElseIf ActiveDocument.Bookmarks("Category").Range.Text = Cat2 Then
    Bookmarks("Category").Range.Shading.BackgroundPatternColorIndex = wdGreen
ElseIf ActiveDocument.Bookmarks("Category").Range.Text = Cat3 Then
    Bookmarks("Category").Range.Shading.BackgroundPatternColorIndex = wdRed
ElseIf ActiveDocument.Bookmarks("Category").Range.Text = Cat4 Then
    Bookmarks("Category").Range.Shading.BackgroundPatternColorIndex = wdYellow
ElseIf ActiveDocument.Bookmarks("Category").Range.Text = Cat5 Then
    Bookmarks("Category").Range.Shading.BackgroundPatternColorIndex = wdPink
End If

Next

End Sub
Cheers to anyone that can help
Reply With Quote
  #2  
Old 06-12-2014, 05:58 PM
macropod's Avatar
macropod macropod is offline Colour code mail merge header table cell backgrounds Windows 7 32bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

When you do a mailmerge, all bookmarks in the output document are deleted. Even if that were not the case, you can't have multiple instances of a given bookmark.

And, since the colouring is dependent on the mergefield results, why not use some field coding in the mailmerge main document to apply those colours as the merge occurs? No vba required.

All you should need is a set of fields, one for each condition, in the relevant cell(s), coded as:

{IF{MERGEFIELD MM}= "Category: 1*" "→↵
→" \* Charformat}

{IF{MERGEFIELD MM}= "Category: 2*" "→↵
→" \* Charformat}

{IF{MERGEFIELD MM}= "Category: 3*" "→↵
→" \* Charformat}

{IF{MERGEFIELD MM}= "Category: 4*" "→↵
→" \* Charformat}

{IF{MERGEFIELD MM}= "Category: 5*" "→↵
→" \* Charformat}

where the:
• → is an actual tab character
• ↵ is a manual line break
• each I in IF is highlighted with the relevant highlight colour
• MM is the data field name
and each cell to be shaded has:
• margins set to 0 all round
• paragraph formatting with a single right-aligned tab-stop set to at least the cell width.
I haven't paid any particular attention to your colour specs, as the above are just to demonstrate the approach. Also, all the fields would be inserted one after the other, without line/paragraph breaks between them - the above separations are just to make the field coding easier to follow.

Note: The field brace pairs (i.e. '{ }') for the above demo field codes are all created in the mailmerge main document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 06-13-2014, 02:55 AM
ScotsMaverick ScotsMaverick is offline Colour code mail merge header table cell backgrounds Windows 7 64bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Novice
Colour code mail merge header table cell backgrounds
 
Join Date: Jun 2014
Posts: 2
ScotsMaverick is on a distinguished road
Default

Thanks macropod, a bit of playing around with the stuff you provided got the solution I needed working, although this method is limited to the 15 highlight colours only as shading doesn't work through mergeformats, which is a bit of a shame.

Cheers
Reply With Quote
  #4  
Old 06-13-2014, 03:05 AM
macropod's Avatar
macropod macropod is offline Colour code mail merge header table cell backgrounds Windows 7 32bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

True, you're limited in the number of highlights you can use, but you could couple that with different font colours too, for a very substantial increase in the possible combinations.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA to immediately change the colour of a cell depending on the code placed in anothe Phil Payne Excel Programming 2 07-27-2013 11:04 PM
Mail Merge Code (Default Display) ochiha_ita Mail Merge 3 04-22-2013 04:04 AM
Colour code mail merge header table cell backgrounds Mail merge in a header Kizzy Mail Merge 1 08-13-2012 10:50 AM
Mail merge Field Code Manipulation macjnr Mail Merge 0 09-10-2009 11:37 AM
Colour code mail merge header table cell backgrounds Code for Changing Cell Backgrounds leroytrolley Excel 2 12-05-2008 02:05 AM


All times are GMT -7. The time now is 01:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft