Microsoft Office Forums

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: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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 (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]
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: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-07-2020, 01:58 AM
FredNerk FredNerk is offline Colour code mail merge header table cell backgrounds Mac OS X Colour code mail merge header table cell backgrounds Office 2016 for Mac
Novice
 
Join Date: May 2020
Posts: 10
FredNerk is on a distinguished road
Default 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
Reply With Quote
  #6  
Old 05-07-2020, 02:08 AM
macropod's Avatar
macropod macropod is offline Colour code mail merge header table cell backgrounds Windows 7 64bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 05-07-2020, 02:34 AM
FredNerk FredNerk is offline Colour code mail merge header table cell backgrounds Mac OS X Colour code mail merge header table cell backgrounds Office 2016 for Mac
Novice
 
Join Date: May 2020
Posts: 10
FredNerk is on a distinguished road
Default

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 ...
Reply With Quote
  #8  
Old 05-07-2020, 02:58 AM
FredNerk FredNerk is offline Colour code mail merge header table cell backgrounds Mac OS X Colour code mail merge header table cell backgrounds Office 2016 for Mac
Novice
 
Join Date: May 2020
Posts: 10
FredNerk is on a distinguished road
Default

OK - well the text appeared as black on white background but no shading of the table cell


Wayne
Reply With Quote
  #9  
Old 05-07-2020, 05:14 AM
macropod's Avatar
macropod macropod is offline Colour code mail merge header table cell backgrounds Windows 7 64bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #10  
Old 05-07-2020, 06:51 AM
FredNerk FredNerk is offline Colour code mail merge header table cell backgrounds Mac OS X Colour code mail merge header table cell backgrounds Office 2016 for Mac
Novice
 
Join Date: May 2020
Posts: 10
FredNerk is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 05-07-2020, 02:13 PM
macropod's Avatar
macropod macropod is offline Colour code mail merge header table cell backgrounds Windows 7 64bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by FredNerk View Post
No different outcome I'm afraid - just coloured text on the white page background of the document.
All that tells me is that you're colouring the text instead of highlighting it. Look at the document in the link I posted.
Quote:
Originally Posted by FredNerk View Post
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.
There is nothing attached to your post.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 05-07-2020, 04:27 PM
FredNerk FredNerk is offline Colour code mail merge header table cell backgrounds Mac OS X Colour code mail merge header table cell backgrounds Office 2016 for Mac
Novice
 
Join Date: May 2020
Posts: 10
FredNerk is on a distinguished road
Default 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
Attached Images
File Type: png Screen Shot 2020-05-07 at 9.43.09 pm.png (106.7 KB, 32 views)
File Type: png Screen Shot 2020-05-07 at 9.48.44 pm.png (112.1 KB, 29 views)
Reply With Quote
  #13  
Old 05-07-2020, 04:35 PM
macropod's Avatar
macropod macropod is offline Colour code mail merge header table cell backgrounds Windows 7 64bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #14  
Old 05-07-2020, 05:21 PM
FredNerk FredNerk is offline Colour code mail merge header table cell backgrounds Mac OS X Colour code mail merge header table cell backgrounds Office 2016 for Mac
Novice
 
Join Date: May 2020
Posts: 10
FredNerk is on a distinguished road
Default

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
Reply With Quote
  #15  
Old 05-07-2020, 05:29 PM
macropod's Avatar
macropod macropod is offline Colour code mail merge header table cell backgrounds Windows 7 64bit Colour code mail merge header table cell backgrounds Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The screenshot of your field code shows shading, not highlighting.
__________________
Cheers,
Paul Edstein
[Fmr 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:50 AM.


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