Format text within a formula

[Larry Horsley]

I am using Excel 2007 SP1.
I have a formula that I want to format the font color of specific text
sections.

Here is my formula:

="change. Payback period will net you " &
IF(OFFSET(NextBuy!$AC$6,ForcastDays,0,1,1)>0,DOLLAR(ABS(OFFSET(NextBuy!$AC$6,ForcastDays,0,1,1)),0)
& " more
than",IF(OFFSET(NextBuy!$AC$6,ForcastDays,0,1,1)<0,DOLLAR(ABS(OFFSET(NextBuy!$AC$6,ForcastDays,0,1,1)),0)
& " less than"," no difference from")) & " ROI, during the next"

What I want to do is format the DOLLAR parts as green for greater than zero,
and red for less than zero. How can I do this?

 

[Eduardo]

hi,
use conditional formating, within the home menu go to Styles (5th tab on
top), conditional formating, highlight cells rules and there apply your rules

If this helps please click yes, thanks

 

[Ron Rosenfeld]

On Wed, 3 Jun 2009 01:37:01 -0700, Larry Horsley <Larry

I am using Excel 2007 SP1.
I have a formula that I want to format the font color of specific text
sections.

Here is my formula:

="change. Payback period will net you " &
IF(OFFSET(NextBuy!$AC$6,ForcastDays,0,1,1)>0,DOLLAR(ABS(OFFSET(NextBuy!$AC$6,ForcastDays,0,1,1)),0)
& " more
than",IF(OFFSET(NextBuy!$AC$6,ForcastDays,0,1,1)<0,DOLLAR(ABS(OFFSET(NextBuy!$AC$6,ForcastDays,0,1,1)),0)
& " less than"," no difference from")) & " ROI, during the next"

What I want to do is format the DOLLAR parts as green for greater than zero,
and red for less than zero. How can I do this?

You cannot do that with a worksheet function.

You can only differentially format the text within a cell if there is an actual
text string within the cell (and not text that is the result of a formula).

So you will need to accomplish your formula within a VBA macro; then write the
resultant text string to the cell; then, using the Characters property, you
will be able to format the particular characters of that string as you wish.
--ron

 

[Ron Rosenfeld]

hi,
use conditional formating, within the home menu go to Styles (5th tab on
top), conditional formating, highlight cells rules and there apply your rules

How does that format just the Dollar parts?
--ron

 

[Eduardo]

Thanks Ron, I missunderstood the question, you are right