#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 (the forum software doesn't support highlighting, so I've had to use font colouring instead) • 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 [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Can you assist please?
Hi there
I am using the following mail merge code in a Word document (Macos). I can get the colour text i need in the table cell but for the life of me cannot work out what it is I am doing when I follow your various advice for colour shading the background table cell. That is, I want the table cell coloured the relevant shade, and keep the text as automatic. { IF { MERGEFIELD LL } = "1" { MERGEFIELD Docname } { IF { MERGEFIELD LL } = "2" MERGEFIELD Docname } { IF { MERGEFIELD LL } = "3" MERGEFIELD Docname } { IF { MERGEFIELD LL } = "4" MERGEFIELD Docname } { IF { MERGEFIELD LL } = "5" MERGEFIELD Docname } } } } } } When I follow the suggested script and substitute the colour of the second MERGEFIELD M to the first I in each IF statement nothing happens with the background cell shading. I am presuming that is happening because I am using the \*CHARFORMAT switch but the test is not alpha based? Any suggestions please? Thank you - much appreciated Wayne |
#6
|
||||
|
||||
You haven't implemented the field coding anything like what was described in post #2. For starters, the field coding demonstrated there has the 'I' of the IF fields highlighted - not shading of the 'M' of the MEREGEREFIELD fields - and your implementation is missing the required \* Charformat switches.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Hi Paul
Yes, what I included was how I got to get the text the colour I was seeking. I tried your suggested script (and similar posts of your s I think form another site), with the changed "IF" statements but I could just not get it working properly. So, following your code example and my variables, will it be as simple as this for just the first conditional statement? { IF {MERGEFIELD DocName} = { " { MERGEFIELD DocName } " \*CHARFORMAT } Sorry, my coding skills are pretty basic ... |
#8
|
|||
|
|||
OK - well the text appeared as black on white background but no shading of the table cell
Wayne |
#9
|
||||
|
||||
As an example:
{IF{MERGEFIELD LL}= "1" "→{MERGEFIELD DocName}→↵ →→" \* Charformat} with I as a highlight, would return DocName in plain text on a green highlighted background. Similarly: {IF{MERGEFIELD LL}= "1" "→{MERGEFIELD DocName}→↵ →→" \* Charformat} with I as a highlight, would return DocName in red text on a yellow highlighted background. See, for example the .docx attachment in: https://www.msofficeforums.com/word-...html#post47254. Although the example there uses a formfield, the principle is the same.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Hi again
I think I have followed your example to the tee - thank you. (Well, in the actual code I have not inserted the tabs and the soft carriage return. They actually create some issues in the output ...) No different outcome I'm afraid - just coloured text on the white page background of the document.. I have attached a screenshot of the mail merge code in the table cell. I have also attached a screenshot of a sample page output. As you can see the text colour is the colour I am hoping the table cell will be shaded. I may just be doing something really simple - but wrong ... Thank you for your assistance ehre - much appreciated Wayne |
#11
|
||||
|
||||
Quote:
There is nothing attached to your post.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Thank you
Morning
Sorry, I just don't get it - I think I have attached the two screen prints from last night correctly this time. I think the logic of all of this sums up why programming was never my forte ... Thank you for all your assistance with this. I look at the other attachment and to me it is as clear as mud - well, perhaps not that muddled, but I still cannot see why this is not working. Cheers |
#13
|
||||
|
||||
Do you not understand the difference between highlighting and shading??? I have repeatedly drawn your attention to the need to use highlighting, not shading.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Of course Paul I understand the difference - what I don't see is any difference in output, and I cannot see what it is I am doing incorrectly.
I am highlighting the "I" of every "IF" statement to, what I expected was, shade in the table cell behind the text. I have left the fieldname as plain (automatic colour) text so that I would end up with a black text on colour filled cell background. I cannot see where I am going wrong here, but please, if you ca, point this out to me in terms of some code perhaps. I am unable to decipher from the online document provided what it is I am missing here. Thank you Regards Wayne |
#15
|
||||
|
||||
The screenshot of your field code shows shading, not highlighting.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
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 |
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 |
Code for Changing Cell Backgrounds | leroytrolley | Excel | 2 | 12-05-2008 02:05 AM |