I'm doing a mail merge of a list of inventory numbers with 3 mergefields to make a sheet of scan labels.
The cells are formatted as:
mergefields.jpg
I'm wanting to change the background color of the cell based on the CLASS mergefield. If the value is 08 I want the background to be colored yellow, otherwise I want the cell to be the default color.
What would be the VBA code for a macro to be able to do this?
Attached is my sample data.
Thanks
See Conditionally Shade Table Cells in the Mailmerge Tips and Tricks thread at:
http://www.msofficeforums.com/mail-m...ps-tricks.html
Last edited by macropod; 05-27-2019 at 02:57 AM.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Unfortunately using merge field conditions as shown in that thread doesn't give me the results I'm looking for.
I tried using the following code:
mergefield condition code.jpg
It sort of works, but the background color doesn't fill the whole cell, and I lose all formatting.
sample output.jpg
The following picture is how I want to cells to look. I assume i need to use VBA code to get this to look the way I want, but I'm not sure how to write it to select based on the mergefield. I'm using Word 2007 if that makes a difference.
desired output.jpg
If you follow the instructions in the link, you'll get the entire cell shaded...
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Hi,
I have followed a lot of threads about this on many sites but I am sorry that I am unable to replicate the successful outcome. Can anyone help me please. I am making a badge that has peoples names and score band on. HIGH should be green, MID orange and LOW RED. I would prefer not to have the words (HIGH, MID and LOW) on the badge. I just need the CELL CONDITIONALLY FORMATTED to the correct colour based on the score. For now I have a database with two fields - Name and Score. The below images are where I am up to with the outcome and code and the way I would like it to be. I need the whole cell filled and preferably not to have the word High in there. Where I am confused is on the mailmerge tips and tricks website it says to create a "Set of fields for each condition". I dont understand why? Especially when the example only uses the mergefield CONDITION and not CONDITION2 or CONDITION3 for each different condition - surely it means set up a single field that has different possibilities that can be picked up by the condition evaluation or comparator? If this is the case do I need to create that field could I not just use an existing field such as my Score field which has 3 possible outcomes (High, Mid, Low)? Another issue I am unable to solve is the TABS. It says use:
{IF{MERGEFIELD Condition}= "1" "→→↵
→Condition 1 Text→↵
→→" \* Charformat}
Where → is an actual TAB. When I press TAB when writing the formula it jumps out of the formula and takes me to the next cell along. Please can a GURU take me under the wing and help me on this? Thank you for reading.
Capture1.jpg
Capture2.jpg
Capture3.jpg
You don't need to have any conditional text, just the required formatting. Note that you need to apply highlighting to the I of IF, not a font colour.Originally Posted by far2many
Because you have 3 conditions and to need an IF test for each. That doesn't imply having a Condition1 field, a Condition2 field, and a Condition3 field; the same field can be tested for all 3 conditions. Hence:
{IF{MERGEFIELD Condition}= "HIGH" "→→↵
→→↵
→→" \* Charformat}{IF{MERGEFIELD Condition}= "MID" "→→↵
→→↵
→→" \* Charformat}{IF{MERGEFIELD Condition}= "LOW" "→→↵
→→↵
→→" \* Charformat}
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Thanks for the advice I managed to get it working from here. The only issue I had left was the way to insert the TAB and Return Character. I copied them from your post and it worked a treat.
Thanks again - looks great!
The characters in my post were not actual tab & return characters, so I can't see how you could have used them and gotten anything that works correctly. As noted in the link, you need to replace those with actual tab & return characters, plus use appropriately-positioned tab-stops.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Hi, I am trying to do the same thing - conditionally shade a table cell in a word mail merge table. I've used the above formatting, but only one word is highlighted, not the whole cell. Any suggestions? I set the table cell margins to 0 but am not sure what the second bullet point means. I cleared the tab stops and set them to right alignment, but this doesn't seem to be working. Is it because there are other merge fields in the same table cell?
Attachment 24080
Your attachment link is invalid. You can attach files via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Here is the attachment.
All your attachment is is an image. There is no way anyone can assess what you've done from that - we'd need the actual mailmerge main document and a description of what is to be formatted. As it is, it's also clear your cells contain text that isn't included within the field code so, whatever else happens, that text isn't going to be highlighted by the field coding.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Thanks, that is what I thought. Is there a way to conditionally format the entire cell background?
Yes, and the instructions in post 6 show how to do that.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
If I am understanding correctly, those instructions don't change the background color for the entire cell, just the text background of the specified field, so that if there are other merge fields or text in the cell, it will only highlight the specified field and not the whole table cell.
I was able to conditionally shade the entire cell by running this VBA macro after the mail merge.
Sub color()
Dim r As Range, text As String, backgroundColor As WdColorIndex
Set r = ActiveDocument.Range
text = "TOXIN"
backgroundColor = wdGray25
With r.Find
Do While .Execute(FindText:=text, MatchWholeWord:=True, Forward:=True) = True
r.Cells(1).Shading.BackgroundPatternColorIndex = backgroundColor
Loop
End With
End Sub
You really do need to pay closer attention to the instructions...
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Please let me know what I am missing. It is not helpful to just say "pay attention to the instructions" as I followed them exactly (or so I thought). I realize I am not an expert at Word but have spent several hours trying to get this to work.
Quite clearly, you have content outside the field construction - which I drew your attention to in post 12 and you conceded in post 13 - whereas the example clearly shows all the content within it. Moreover, I explicitly told you in post 12 why your approach won't work.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
AbbyPi.
It does work.
To get the concept just forget about merge fields for a minute.
1. Create a 1 row x 2 column table.
2. Set the cell margins right, left, top and bottom to 0 pts.
3. Create a simple conditional IF field as shown.
4. Format the "I" in "IF" with yellow highlight
5. Insert a right aligned tab space just passed the right border of cell 1.
2019-04-19_17-11-00.jpg
6. Select all, update fields and toggle field codes.
7. Since the condition is met, the entire content of the cell will be highlighted yellow.
8. Toggle fields codes, change 1 = 1 to 1 = 2. Repeat select, update toggle and the highlighting goes away.
I agree that this works and I had no problem getting it to work with only one field in the cell.
However, because it uses text highlighting and not cell background shading, it does not work with additional text outside the field code, which was pointed out and which is what I was hoping to solve by asking for help in this forum. I apologize if my question was not clear.
Posting Permissions
Reply With Quote
