Solved: Conditional Formatting Question

[Cyberdude]

The following formula:
=TEXT(0.05, "+0%") & " = " & TEXT(1.05 * RefPrice2, "$0.00") &
" (=" & TEXT(1.05 * RefPrice2 * Shares2 - BrkEven2* Shares2, "+$#,##0;-$#,##0") & ")" creates an output line that looks like this:

+5% = $6.76 (= -$4,600)

I would like the line to look like this:

+5% = $6.76 (= -$4,600)

In other words, I want negative dollar values to be red, but I can’t seem to find a way to do it. Any ideas?

I have a macro that creates a table of these lines (0% to 10%). In some tables I get a mixture of positive and negative dollar values, and I would like it very evident when a dollar amount is negative.

[Cyberdude]

I'll accept an answer like:
"It can't be done".
I just want confirmation, please.

[TrippyTom]

does it work if you use this?


=TEXT(0.05, "+0%") & " = " & TEXT(1.05 * RefPrice2, "$0.00") &



" (=" & TEXT(1.05 * RefPrice2 * Shares2 - BrkEven2* Shares2, "+$#,##0;[Red]-$#,##0") & ")"

[Bob Phillips]

Don't think it can be done without code. Adding conditional formats to TEXT does not seem to work.

[mdmackillop]

Hi Sid,
I concur that either a sub or a UDF is needed here.

[Cyberdude]

Thanks, gang! Somehow I had a feeling that's what you'd say.