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

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

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

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

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

In .com, Cortez
spake thusly:

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.

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: , with the
Subject: Custom accounting format . Oh, well.)

-dman-

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

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 !
---------------------------------------------------------------------------
"Cortez" wrote in message
ups.com...
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

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

Thanks so much.
cTK


Dallman Ross wrote:
In .com, Cortez
spake thusly:

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.

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: , with the
Subject: Custom accounting format . Oh, well.)

-dman-