Tricky: Word Table Conditional Formatting

[TomorrowsMan]

Hi there,

I've got a stumper.....

First, I have a Word document with several form fields. Some of these
fields calculate into subsequent bookmarks.

The problem: At the end of the Word document, I have a simple "Overall
Score" table that is 2x4, laid out as such:

Ranking Rating
Fair 0-.99
Good 1-1.99
Great 2-2.99
Excellent 3-4.00

The rating is based on the value of a bookmark (GrandTotal) calculated
by a macro earlier in the document; the rating can be from 0-4 in
half-point increments. So, an average rating would be, for instance,
2.5, which would be a ranking of "Great."

What I need to happen is this: Once the "GrandTotal" bookmark
calculates (which it does OnExit from a previous form field), I would
like the associated row and column in the "Overall Score" table to
highlight; so, if the GrandTotal is 2.5, then "Great" and "2-2.99"
would become boldface text with a light blue background.

I have done this easily and often in Excel with conditional formatting
formulas, but I do not know how to embed the Excel table in Word and
have the conditional formatting run off of the Word table bookmark
"GrandTotal."

Is this possible; or, is there a way to do it with VBA in Word? Or, is
there some way to 'hide' the Word bookmark value is the embedded Excel,
so I can run the conditional formatting in there?

Cheers,
TomorrowsMan

 

[Jezebel]

Bookmark each row of your overall score table. In your OnExit macro, use
something like ActiveDocument.Bookmarks("Great").Range.Bold = TRUE

 

[TomorrowsMan]

Thank you!

I am new at this, and didn't realize I could assign bookmarks to
non-form fields.

So, would I just write a formula into the macro, something like (and
I'm thinking in Excel here, not too keen on VBA yet):

If Val.ActiveDocument.Bookmarks("GrandTotal") < 1
ActiveDocument.Bookmarks("Fair").Range.Bold = TRUE
ActiveDocument.Bookmarks("Fair").Range.BackgroundPatternColor =
wdColorBlue

?

And I guess that's my next big stumper; how do I get the OnExit macro
to read the value of the GrandTotal bookmark, pick it out of the ranges
(GrandTotal is less than 1; between 1 and 1.99; between 2 and 2.99; or
over 2.99), then apply the formatting to the corresponding range of
cells?

Thanks again!!

Chris

 

[Jezebel]

Select case GrandTotal
Case Is < 1
...
Case Is < 2
...

etc

 

[TomorrowsMan]

Sorry you have been having to lead me by the hand through this; I'm
just discovering how versatile VBA is when making user forms, and I'm
not versed in the language yet.

So, if I take a stab at it.....I would have:

Sub FieldHighlight()
Select case GrandTotal
Case Is < 1
ActiveDocument.Bookmarks("Fair").Range.Bold = TRUE
ActiveDocument.Bookmarks("Fair").Range.BackgroundPatternColor =
wdColorBlue
Case Is < 2
ActiveDocument.Bookmarks("Good").Range.Bold = TRUE
ActiveDocument.Bookmarks("Good").Range.BackgroundPatternColor =
wdColorBlue
Case Is < 3
ActiveDocument.Bookmarks("Great").Range.Bold = TRUE
ActiveDocument.Bookmarks("Great").Range.BackgroundPatternColor =
wdColorBlue
Case Is < 4
ActiveDocument.Bookmarks("Excellent").Range.Bold = TRUE
ActiveDocument.Bookmarks("Excellent").Range.BackgroundPatternColor =
wdColorBlue
End Sub

Is that close? Or am I missing a line selecting the GrandTotal
bookmark?

Thanks again a million times....I'm learning alot, but there's a lot to
learn.

Chris

 

[Jezebel]

You'll need to retrieve the value first -- VBA doesn't know what
'GrandTotal' refers to.

Dim pValue as double
Dim pBookmark as string

pValue = ActiveDocument.Bookmarks("GrandTotal").Range
Select case pValue
Case Is < 1
pBookmark = "Fair"
:
End Select

With ActiveDocument.Bookmarks(pBookmark).Range
.Bold = True
.BackgroundPatternColor = wdColorBlue
End with

 

[TomorrowsMan]

Thank you so very much!

Cheers!

 

[macropod]

Hi TomorrowsMan,

You can also do this without vba, using formula fields. For example, press
Ctrl-F9 three times to create a set of three nested fields:
{ { { } } }
then embed more fields within these so that you get:
{ { } { } { { } { } { { } { } { } }}}
and fill them out so that they look like:
{IF{GrandTotal}< 1 "{GrandTotal \# 0.00} Fair" {IF{GrandTotal}< 2
"{GrandTotal \# 0.00} Good" {IF{GrandTotal}< 3 "{GrandTotal \# 0.00} Great"
"{GrandTotal \# 0.00} Excellent"}}}
then apply the desired formatting to each of the output strings (eg
"{GrandTotal \# 0.00} Good").

Now, when you update the 'GrandTotal', the output will display in the
desired format. You can nest up to 20 IF tests this way in Word.

Cheers

 

[Jezebel]

Hilarious.

Hi TomorrowsMan,

You can also do this without vba, using formula fields. For example, press
Ctrl-F9 three times to create a set of three nested fields:
{ { { } } }
then embed more fields within these so that you get:
{ { } { } { { } { } { { } { } { } }}}
and fill them out so that they look like:
{IF{GrandTotal}< 1 "{GrandTotal \# 0.00} Fair" {IF{GrandTotal}< 2
"{GrandTotal \# 0.00} Good" {IF{GrandTotal}< 3 "{GrandTotal \# 0.00}
Great"
"{GrandTotal \# 0.00} Excellent"}}}
then apply the desired formatting to each of the output strings (eg
"{GrandTotal \# 0.00} Good").

Now, when you update the 'GrandTotal', the output will display in the
desired format. You can nest up to 20 IF tests this way in Word.

Cheers