Tricky: Word Table Conditional Formatting

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

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




"TomorrowsMan" wrote in message
oups.com...
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

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.BackgroundP atternColor =
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

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

etc





"TomorrowsMan" wrote in message
oups.com...
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.BackgroundP atternColor =
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

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.BackgroundP atternColor =
wdColorBlue
Case Is 2
ActiveDocument.Bookmarks("Good").Range.Bold = TRUE
ActiveDocument.Bookmarks("Good").Range.BackgroundP atternColor =
wdColorBlue
Case Is 3
ActiveDocument.Bookmarks("Great").Range.Bold = TRUE
ActiveDocument.Bookmarks("Great").Range.Background PatternColor =
wdColorBlue
Case Is 4
ActiveDocument.Bookmarks("Excellent").Range.Bold = TRUE
ActiveDocument.Bookmarks("Excellent").Range.Backgr oundPatternColor =
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

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" wrote in message
ups.com...
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.BackgroundP atternColor =
wdColorBlue
Case Is 2
ActiveDocument.Bookmarks("Good").Range.Bold = TRUE
ActiveDocument.Bookmarks("Good").Range.BackgroundP atternColor =
wdColorBlue
Case Is 3
ActiveDocument.Bookmarks("Great").Range.Bold = TRUE
ActiveDocument.Bookmarks("Great").Range.Background PatternColor =
wdColorBlue
Case Is 4
ActiveDocument.Bookmarks("Excellent").Range.Bold = TRUE
ActiveDocument.Bookmarks("Excellent").Range.Backgr oundPatternColor =
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

Thank you so very much!

Cheers!

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


"TomorrowsMan" wrote in message
oups.com...
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

Hilarious.



"macropod" wrote in message
...
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


"TomorrowsMan" wrote in message
oups.com...
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