Consulting

Results 1 to 17 of 17

Thread: Conditionally Format Font Color or Cell Shading in a Table

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    Conditionally Format Font Color or Cell Shading in a Table

    Okay.
    Suppose I need conditional formatting in a column of cells in a table.

    If the cell's blank, do nothing.
    If the date in the cell is, for instance, today or older, the text becomes red or the cell gets shaded pink or some such. The specifics can be whatever you want, we're just looking for a way for the user to show emphasis...

    Anybody got any ideas? I would think running the macro on document_open would be okay.
    ~Anne Troy

  2. #2
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Most of the credit for this procedure goes to Tommy. He helped me with something similar.

    This code assumes:
    * Table # = 1
    * Column 5 contains the date to test

    [vba]
    Sub HighLightColumnOfCells()
    RightNow = Format(Now, "mm/dd/yyyy")
    For I = 1 To ActiveDocument.Tables(1).Rows.Count
    DueDate = Format(Left$(ActiveDocument.Tables(1).Cell(I, 5).Range.Text, Len(ActiveDocument.Tables(1).Cell(I, 5).Range.Text) - 1), "mm/dd/yyyy")

    If ActiveDocument.Tables(1).Cell(I, 5).Range.Text = Chr(13) & Chr(7) Then
    'Cell is blank, color it yellow
    ActiveDocument.Tables(1).Cell(I, 5).Shading.BackgroundPatternColor = wdColorYellow

    ElseIf DueDate <= RightNow Then
    'Cell is blank, color it red
    ActiveDocument.Tables(1).Cell(I, 5).Shading.BackgroundPatternColor = wdColorRed

    ElseIf DueDate >= RightNow Then
    'Cell is blank, color it Green
    ActiveDocument.Tables(1).Cell(I, 5).Shading.BackgroundPatternColor = wdColorGreen
    End If
    Next I

    End Sub
    [/vba]

    Cheers,
    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  3. #3
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    James, thanks!
    I posted this at the question in question.
    LOL!!
    ~Anne Troy

  4. #4
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I like the idea of conditional formatting in Word - could we develop it as part of the Add-In? There's a lot to consider I guess.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  5. #5
    VBAX Regular
    Joined
    Jul 2004
    Location
    San Bernardino, California
    Posts
    69
    Location
    hmmm . . . I'm not sure where you guys are on this Word Addin as I haven't been looking at that thread, but I'm thinking a class module (converted to a .dll) could easily handle some conditional formatting on this.

    Maybe have some let properties in the function for setting the table's name, columns to be formatted, and then some criteria section???

    Sound like what you guys are talking about?

  6. #6
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I'm not involved either - I have taken a copy of what Gerry has posted but not looked at it yet - I just thought the idea had potential if it could be generalised.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  7. #7
    VBAX Regular
    Joined
    Jul 2004
    Location
    San Bernardino, California
    Posts
    69
    Location
    Quote Originally Posted by TonyJollans
    I'm not involved either - I have taken a copy of what Gerry has posted but not looked at it yet - I just thought the idea had potential if it could be generalised.
    I don't think I'll be able to get involved in helping with the addin (just too busy), but I might be able to come up with a .dll that can be used to do this.

    I'll take a deeper look this evening when I get back to my comp.

  8. #8
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Maybe have some let properties in the function for setting the table's name, columns to be formatted, and then some criteria section???
    Unfortunately, I don't think most users will know the tables's name, or do you mean GIVING it a name?
    ~Anne Troy

  9. #9
    VBAX Regular
    Joined
    Jul 2004
    Location
    San Bernardino, California
    Posts
    69
    Location
    hmmm . . . OK, So there you go putting me on the spot again, LOL

    Well I am not a big Word Person but I assumed there was a name property, but there's not. So I guess I'd have to go on the index, which is fine.

    I'll go deeper into it this evening!

  10. #10
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    No problem. As for COM addin, I'm not sure we decided on that yet. These are freebies that will hopefully draw attention and donations to the site.

    Not only that, but we could look at the addins as *free advertising* for the coders who produce them; hopefully getting people to look for those programmers here in our Jobs forum, particularly if the addin does something CLOSE to what they want.

    The addins are also a great way to let non-coders learn the capabilities of code.
    ~Anne Troy

  11. #11
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Hi all, Im not sure of the point in creating conditional formatting in a table within Word. Did you know you could insert an Excel sheet and the embedded object can have conditional formatting applied to this using Excels menus which appear when the object is selected?

  12. #12
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Here is how I used conditional formatting in Word to solve a problem. One of our company's Word templates is a Call Tracking document. The document contains a table used to track Action Items related to customer calls. Each action item has a due date assigned to it. As the action item list grows, keeping track of approaching due dates and completion dates becomes more difficult.

    We used the conditional formatting code as part of the document's Auto_Open procedure to check the due date of each action item and highlight rows where the due date is old (RED), where the due date is near or today (YELLOW), and when an item is complete (GREEN).

    Our users love this new feature because it saves them time from manually highlighting items and automatically draws attention to past due and pending items.

    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  13. #13
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location

    question for James

    James,
    Your code works for me nicely but I have a couple of quick questions if you don't mind helping me to understand...

    The cells are yellow if they are blank but in the code it says
    [VBA]
    Chr(13) & Chr(7) Then 'Cell is blank, color it yellow
    [/VBA] what charactors are Chr(13) & Chr(7)

    You also said that you had changed your code to turn yellow if the due date was in the near future but I have not been able to implement this. This is where I am now but I can't get it to turn yellow for up to 5 days in the future?!

    [VBA]ElseIf DueDate >= " & DateAdd("d", 5, "RightNow") Then
    [/VBA]
    could you give me a lead as this is not working for reasons that will probably be obvious to you. Have been reading help files and will continue trying to figure this out.

    Also...as usual, Parry had great insight about inserting an excel spreadsheet object into word. then you can copy and paste the cells with conditonal formatting into the inserted spreadsheet. Works great, updates just like excel and...looks nice too.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  14. #14
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Quote Originally Posted by parry
    Hi all, Im not sure of the point in creating conditional formatting in a table within Word. Did you know you could insert an Excel sheet and the embedded object can have conditional formatting applied to this using Excels menus which appear when the object is selected?
    Hi, parry!

    The thing about Excel is that it isn't suited to lots of text, and certainly not with any half decent document formatting.

    You other guys:

    I was discussing something similar with a client. They have a table of rows with one form field each. When the doc is complete, we'd like it to find each blank formfield and say "would you like to hide this row?", and if so, hide it. If not, force entry of a value into the form field. This is because they have values for X or Y, but not BOTH X and Y. The form now allows for entry of either, but the final form doesn't show a non-relative row. Suppose I have a mortgage application checklist. In it, REntal Agreement or Mortgage Agreement are X and Y. If Rental agreement is blank, then we want to remove that row. Unfortunately, we discussed having Rental or Mortgage as a dropdown, but he didn't like that idea...

    Just some thoughts/ideas for the addin/etc.
    ~Anne Troy

  15. #15
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Hi Lucas,

    The "Chr(13) & Chr(7)" combo is used to determine if the cell is blank. Word inserts these non-printing characters in each table's cell by default. These characters tripped me up for a while until Tommy told me about them.


    I modified my sample for you to detect when the DueDate is withing 5 days of RightNow. There are two reasons why the code you posted won't work. The first is syntax - you need to remove the first " and the &, as well as the quote around RightNow (RightNow is a variable - by placing quotes around it your make it a literal string). So it should have initially read like:
    [vba]
    ElseIf DueDate >= DateAdd("d", 5, RightNow) Then
    [/vba]

    If you had written the statement like that, you then would have a logic error because the statement would have highlighted any rows >= 5 days in the future, not just rows within 5 days. You need an AND statement to test for that condition.

    Also, since this is not an exact copy of the statement I actually used, you may need some further logical testing. For instance, if there is a Date Complete cell, you would want to make sure it isn't blank when you test the DueDate. Otherwise, you could end up marking an item RED when it is actually complete. Just modify the same AND statement in the sample code to suit your situation.

    [vba]
    Sub HighLightColumnOfCells()
    Dim DueDate, RightNow As Date

    RightNow = Format(Now, "mm/dd/yyyy")

    For I = 1 To ActiveDocument.Tables(1).Rows.Count
    DueDate = Format(Left$(ActiveDocument.Tables(1).Cell(I, 5).Range.Text, Len(ActiveDocument.Tables(1).Cell(I, 5).Range.Text) - 1), "mm/dd/yyyy")

    If ActiveDocument.Tables(1).Cell(I, 5).Range.Text = Chr(13) & Chr(7) Then
    'Cell is blank, color it white
    ActiveDocument.Tables(1).Cell(I, 5).Shading.BackgroundPatternColor = wdColorWhite

    ElseIf DateDiff("d", RightNow, DueDate) >= 1 And DateDiff("d", RightNow, DueDate) <= 5 Then
    'DueDate is within 5 days, color it yellow
    ActiveDocument.Tables(1).Cell(I, 5).Shading.BackgroundPatternColor = wdColorYellow

    ElseIf DueDate <= RightNow Then
    'DueDate is today, color it red
    ActiveDocument.Tables(1).Cell(I, 5).Shading.BackgroundPatternColor = wdColorRed

    ElseIf DateDiff("d", RightNow, DueDate) > 5 Then
    'DueDate is more than 5 days in the future, color it Green
    ActiveDocument.Tables(1).Cell(I, 5).Shading.BackgroundPatternColor = wdColorGreen
    End If
    Next I

    End Sub
    [/vba]

    Cheers,
    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  16. #16
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Thank you Much James,

    The changes you made work great. I knew I had some problems with the syntax. Excel kept telling me the things I was doing wrong. I'm reading a couple of books and trying to get something out of the help files so I can figure these problems out for myself. You guys won't always be around for me to lean on. Seriously, sometimes it helps to see how a task is achieved to be able to put it all together. Thanks for your insights. I'll be glad when I know what is wrong with my syntax instead of just knowing that its wrong! someday. The practice of trying to get these files to do what I want and knowing that I have good people to ask for direction when I get stumped sure makes learning easier.

    Parry also has a post here about inserting excel worksheets in Word. It works pretty nicely also. I didn't know about it before but as Parry pointed out, after you insert the worksheet word makes the format-conditional formatting menu available in Word. Pretty handy to know.
    Thanks again
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  17. #17
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Quote Originally Posted by Dreamboat
    Hi, parry!

    The thing about Excel is that it isn't suited to lots of text, and certainly not with any half decent document formatting.
    Fair enough Sweetie.
    Just because I have never needed to do conditional formatting in Word doesnt mean other people wont so good on you for thinking outside the square.

Posting Permissions

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