Changing text color within a formula???

[Cortez]

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

 

[David Biddulph]

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.

 

[Cortez]

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

 

[sshankle]

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]

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

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

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

 

[Dallman Ross]

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

You may not have understood part of the implication in
what David had said. You can, indeed, have your negative
numbers be red without using Conditional Formatting. You
just use regular formatting. E.g., look at the 4th choice
(in Excel 2002, anyway) for number formatting. If you
format your cells with that, then negative numbers will
be red and in parentheses. If you choose the second format,
then they'll be red, but sans parens.

You can also use a custom format (not the same as conditional
formatting!) to add a minus sign instead of parens, if you
want.

(This thread is reminding me that I'm disappointed nobody
who might understand these custom format codes better than
I do has yet helped me with my question of last week in
Message-ID: <[email protected]>, with the
Subject: Re: Custom accounting format . Oh, well.)

-dman-

 

[Dallman Ross]

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

==========================================================

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

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

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

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

 

[Ragdyer]

If your text is a constant, you can make it part of the actual format and
have the color displayed with the text.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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

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

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

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

 

[Cortez]

The text unfortunately isn't consistent enough to use formatting. I
think I will pursue a VBA solution.

Thanks so much.
cTK