Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: Mail Merge - Conditional formatting of cell background color

  1. #1
    VBAX Newbie
    Joined
    Jul 2018
    Posts
    2
    Location

    Mail Merge - Conditional formatting of cell background color

    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
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    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
    [MS MVP - Word]

  3. #3
    VBAX Newbie
    Joined
    Jul 2018
    Posts
    2
    Location
    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

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    If you follow the instructions in the link, you'll get the entire cell shaded...
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    VBAX Newbie
    Joined
    Nov 2018
    Posts
    2
    Location
    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


  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    Quote Originally Posted by far2many View Post
    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.
    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.
    Quote Originally Posted by far2many View Post
    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?
    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
    [MS MVP - Word]

  7. #7
    VBAX Newbie
    Joined
    Nov 2018
    Posts
    2
    Location

    Thanks - All sorted!

    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!

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    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
    [MS MVP - Word]

  9. #9
    VBAX Regular
    Joined
    Mar 2019
    Posts
    7
    Location
    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

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    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
    [MS MVP - Word]

  11. #11
    VBAX Regular
    Joined
    Mar 2019
    Posts
    7
    Location

    Trying to highlight entire cell background in mail merge table

    Quote Originally Posted by macropod View Post
    Your attachment link is invalid. You can attach files via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
    Here is the attachment.

  12. #12
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    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
    [MS MVP - Word]

  13. #13
    VBAX Regular
    Joined
    Mar 2019
    Posts
    7
    Location
    Quote Originally Posted by macropod View Post
    All your attachment is is an image. 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.
    Thanks, that is what I thought. Is there a way to conditionally format the entire cell background?

  14. #14
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    Yes, and the instructions in post 6 show how to do that.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  15. #15
    VBAX Regular
    Joined
    Mar 2019
    Posts
    7
    Location
    Quote Originally Posted by macropod View Post
    Yes, and the instructions in post 6 show how to do that.
    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

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    Quote Originally Posted by AbbyPi View Post
    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.
    You really do need to pay closer attention to the instructions...
    Cheers
    Paul Edstein
    [MS MVP - Word]

  17. #17
    VBAX Regular
    Joined
    Mar 2019
    Posts
    7
    Location
    Quote Originally Posted by macropod View Post
    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.
    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.

  18. #18
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,867
    Location
    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
    [MS MVP - Word]

  19. #19
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,021
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  20. #20
    VBAX Regular
    Joined
    Mar 2019
    Posts
    7
    Location
    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

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •