Consulting

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

Thread: Mail Merge - Conditional formatting of cell background color

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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
    4,435
    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
    [Fmr 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
    4,435
    Location
    If you follow the instructions in the link, you'll get the entire cell shaded...
    Cheers
    Paul Edstein
    [Fmr 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
    4,435
    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
    [Fmr 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
    4,435
    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
    [Fmr 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
    4,435
    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
    [Fmr 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
    4,435
    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
    [Fmr 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
    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.

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

  16. #16
    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
    Last edited by Aussiebear; 04-02-2023 at 12:54 PM. Reason: Added code tags to supplied code

  17. #17
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    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
    [Fmr MS MVP - Word]

  18. #18
    VBAX Newbie
    Joined
    Aug 2022
    Posts
    2
    Location

    Multiple actions

    "This worked like a dream! I am very rusty with VBA though, can someone please tell me how to ask it to perform multiple actions. I forget which part of the code I need to copy for it to repeat.

    I would like it to find the following and shade the cell that colour:

    text = "W4"
    backgroundColor = wdRed


    text = "W5"
    backgroundColor = wdRed


    text = "W6"
    backgroundColor = wdYellow


    text = "W7"
    backgroundColor = wdYellow


    text = "W8"
    backgroundColor = wdGreen


    text = "W9"
    backgroundColor = wdGreen


    Thanks


    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.

    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

  19. #19
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Nikko77 View Post
    "This worked like a dream! I am very rusty with VBA though, can someone please tell me how to ask it to perform multiple actions. I forget which part of the code I need to copy for it to repeat.

    I would like it to find the following and shade the cell that colour:
    The solution discussed extensively in this thread requires no VBA.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  20. #20
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    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
    [Fmr MS MVP - Word]

Posting Permissions

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