Format text within a formula

  • Thread starter Thread starter Larry Horsley
  • Start date Start date
L

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?
 
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
 
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?
Click to expand...

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
 
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
Click to expand...

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