Ah, your further explanation wasn't there when I posted my
other reply just now. Oh, well.

Still, you can color the numbers in such strings in VBA code.
I can't provide an example, but this week a similar thread
was posted here with example code.

-dman

================================================== ========
In . com, Cortez
spake thusly:

Thanks, great idea. Sadly this option is still not a solution. This
would work for the simple example I gave, but I should have been more
descriptive.

I have a complex formula, and it spits out text that includes text and
numbers. The end result is a sentence where the numeric values need to
be color coded, but the surrounding text doesn't.

A better example is this: ="Leading text " & if(as<0,RED(a1), a1) & "
trailing text"

I'd like to be able to have the text in black, and the value of A1 in
red only when A1 is a negative value. I suspect that this isn't
possible with excel, but your idea will be useful in the future no
doubt.

Thanks,
cTK


sshankle wrote:
But the trick you need is wrapped in the number format of the cell itself...
You can use one of the 'custom' formats to color positve, and negative any
way you prefer.
ie some variant of this: $#,##0_);[Red]($#,##0)

sshankle


"Cortez" wrote:

Thanks, if it can't be done, I'll stop trying. Conditional formating
won't cut it for this purpose unfortunately.

David Biddulph wrote:
You can't change colour with a formula in a cell. You can either use cell
formatting (Format/ Cells) which include options for negative to be red, or
you can use Conditional Formatting (Format/ Conditional Formatting) where
you can specify your own conditions and formats.
--
David Biddulph

"Cortez" wrote in message
oups.com...
I have a formula that displays text and values, and I would like to
format the values so they are red if they are negative.

example: =if(a1<0,red(a1),a1) where the word red is to indicate that I
would like that text to show in red.

Not sure if my description is adequate, but I would be happy to clarify
if required.

Thanks in advance,
cTK