Printable View
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.
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
James, thanks!
I posted this at the question in question.
LOL!!
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.
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?
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.Quote:
Originally Posted by TonyJollans
I'll take a deeper look this evening when I get back to my comp.
Unfortunately, I don't think most users will know the tables's name, or do you mean GIVING it a name?Quote:
Maybe have some let properties in the function for setting the table's name, columns to be formatted, and then some criteria section???
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!
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.
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?
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
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.
Hi, parry! :beatQuote:
Originally Posted by 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.
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
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
Fair enough Sweetie. :whipQuote:
Originally Posted by Dreamboat
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.:yes :yes