Multicolored Dynamic Text

  • Thread starter Thread starter hall.jeff
  • Start date Start date
H

hall.jeff

I'm trying to build a check for customers that will highlight verbiage
when something isn't allowed... This would be my optimal solution

="Currently it adds up to "&TEXT(D14,"0.0")&" which is
"&IF(MOD(D14,1)=0,"",text("NOT ","[Red]"))&"a whole number."

The part of interest there is the text("NOT ","[Red]") function... I'd
like the "NOT" to appear in red with the rest showing up in black but
this doesn't seem possible... I also tried doing it through VBA but
that proved to not work either...

Any ideas?
 
It's not possible to do what you want with a formula. A macro can do it:

ActiveCell.Value = "This is RED text"
ActiveCell.Characters(9, 3).Font.ColorIndex = 3


--
Jim
| I'm trying to build a check for customers that will highlight verbiage
| when something isn't allowed... This would be my optimal solution
|
| ="Currently it adds up to "&TEXT(D14,"0.0")&" which is
| "&IF(MOD(D14,1)=0,"",text("NOT ","[Red]"))&"a whole number."
|
| The part of interest there is the text("NOT ","[Red]") function... I'd
| like the "NOT" to appear in red with the rest showing up in black but
| this doesn't seem possible... I also tried doing it through VBA but
| that proved to not work either...
|
| Any ideas?
 
That's what I figured... I really don't want to have to deal with the
overhead associated with a _SheetChange or _SheetCalculate scenario...
I think I'm stuck rewriting the stupid thing...

Thank you
 
Jeff-

A formula (using either built-in Excel functions or a UDF) cannot return a
partially formatted string. Or any kind of format. Formulas return values
to cells and that's it.

Take that as a challenge if you want<g>.

--
Jim
Jeff Hall said:
Have you considered writing a function, rather than a macro?

This way, you can pass D14 as a parameter, calculate the cell contents and
then format the cell. I suspect you will have to pass the address of the
active cell to the function because "ActiveCell" won't be appropriate in
the function.

Regards
Jeff Hall (coincidence!)


*From:* (e-mail address removed)
*Date:* Thu, 3 Apr 2008 13:25:24 -0700 (PDT)

That's what I figured... I really don't want to have to deal with the
overhead associated with a _SheetChange or _SheetCalculate scenario...
I think I'm stuck rewriting the stupid thing...
Click to expand...
Thank you

*From:* "Jim Rech" <[email protected]>
*Date:* Thu, 3 Apr 2008 16:19:33 -0400

It's not possible to do what you want with a formula. A macro can do it:

ActiveCell.Value = "This is RED text"
ActiveCell.Characters(9, 3).Font.ColorIndex = 3


--
Jim
| I'm trying to build a check for customers that will highlight verbiage
| when something isn't allowed... This would be my optimal solution
|
| ="Currently it adds up to "&TEXT(D14,"0.0")&" which is
| "&IF(MOD(D14,1)=0,"",text("NOT ","[Red]"))&"a whole number."
|
| The part of interest there is the text("NOT ","[Red]") function... I'd
| like the "NOT" to appear in red with the rest showing up in black but
| this doesn't seem possible... I also tried doing it through VBA but
| that proved to not work either...
|
| Any ideas?
Click to expand...
Click to expand...
 
Back
Top