Picky Boss! Need code snippet for color, please...

[Angyl]

After sweating and slaving to hack out a custom form in Word2003 and a
UserForm on top of it, my boss is not satisfied with the simple calculation
field that shows a difference between two amounts as DUE $12.34 or OWED
($12.34)

HE WANTS IT COLOR CODED! Red for amount owed and Green if money is due us.

*sigh*

Anyone know how best to code something like that? Is it even possible? (I'd
be happy to go back and tell him it can't be done).

I figure it could work as either a part of the VB code in the userform
command button that inputs the data (check that box and color appropriately)
or as a Macro that runs automatically when the userform is closed, or it
could be coded into the form field itself.

I know specifics are appreciated with these types of questions so the three
form fields I'm working with are bookmarked:
"Total"
"TotalPaid"
"OweDue"

Thanks in advance for your help!

 

[Greg Maxey]

If you are using formfields, the you can use a formatting switch:

E.g., { (=5+5) \# "#.00;(#.00);0" }

Format tthe first #.00 green, the (#.00) red and the 0 black

 

[Angyl]

I'm sorry, Greg, you lost me. I have one bookmarked field (TotalPremiumDue)
and a second one (TotalCollected). Right now what I have is a third field
that runs this calculation:
=(TotalCollected)-(TotalPremiumDue)

It gets the job done, but after kicking around your formula below, I can't
seem to apply it properly, so that if the number is ($0.00) negative, it is
red and $0.00 positive, green.

Thanks for all your help!

 

[Dave Lett]

Angyl,

Greg's suggestion had a misplaced open parens.
Try this:
=(5+5) \# "#.00;(#.00);0"
=(5-10) \# "#.00;(#.00);0"

Copy this to a document. Hightlight the first sample and press CTRL + F9 (to
create a field) and then press F9 (to update the field).
Do the same for the second sample.
Turn on reveal field codes.
In each sample, highlight the first "#.00" and change the font color to
green.
In each sample, highlight "(#.00)" and change the font color to red.
Put the cursor in each sample, press F9, and then turn off reveal field
codes.

HTH,
Dave

 

[Angyl]

Works! Thanks, Dave!

Angyl,

Greg's suggestion had a misplaced open parens.
Try this:
=(5+5) \# "#.00;(#.00);0"
=(5-10) \# "#.00;(#.00);0"

Copy this to a document. Hightlight the first sample and press CTRL + F9 (to
create a field) and then press F9 (to update the field).
Do the same for the second sample.
Turn on reveal field codes.
In each sample, highlight the first "#.00" and change the font color to
green.
In each sample, highlight "(#.00)" and change the font color to red.
Put the cursor in each sample, press F9, and then turn off reveal field
codes.

HTH,
Dave

 

[Greg Maxey]

Here again don't use a calculation field. You a straight formula field
with Ref fields to the bookmarks:

{ = { Ref Text1 }+{ Ref Text2 } \# "#.00;-#.00;0"}

Replace Text1 adn Text2 with your bookmark names. Format the -#.00
with red font.

You will need to set 0 as the default value for the formfields to mask
a syntax error if either is blank.

 

[Angyl]

You're the Best, Greg!