Changing Formula Color NOT cell Color in Formula Bar

[Lucas1234]

Hi,

I know you can change cell colors and conditional formatting, but can change the formula text color (inside the formula bar) and NOT the cell color?

For example ...

Inside my formula bar:
=56.99+27.34+910.18+12.99

Inside my cell (that displays the sum total):
3714.16 (Note that I want this color displayed at normal black)

Is this possible?

Thank you.

[olasa]

Not easily.
It is possible to change the Text color. Text can transformed to Numbers.
It all depends how important it is to have another color.

HTH
Ola

[Lucas1234]

Thank you for the response.

It is important for me to change the color as I need to track estimated costs. These estimated costs will be highlighted in red for future change. Can you describe the process in doing this?

Thanks!

[shg]

You can't have text of varying colors in the formula bar.

Rather than hard-code values in formulas, you can put the numbers in separate cells, and then color-code those as you wish.

Or you could do

=56.99 + 27.34 + 910.18 + N("Estimated") + 12.99

[olasa]

Here is one option (see encl. file)
It's not a very nice solution - that is an understatement - but it can sum text values.
The only thing is that the text values has to have a + sign to start and to end each value. Apart from that each value can be colored.

Example:
Text: '=+10000+1000+100+10+
Result: 11110

Formula:

=SUMPRODUCT(--MID(A2;
LARGE(ROW(INDIRECT(LEN(A2)&":1"))*(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)="+");
(LEN(A2)-LEN(SUBSTITUTE(A2;"+";"")))+1-ROW(INDIRECT("2:"&(LEN(A2)-LEN(SUBSTITUTE(A2;"+";"")))))+1);
LARGE(ROW(INDIRECT(LEN(A2)&":1"))*(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)="+");
(LEN(A2)-LEN(SUBSTITUTE(A2;"+";"")))-ROW(INDIRECT("2:"&(LEN(A2)-LEN(SUBSTITUTE(A2;"+";"")))))+1)-
LARGE(ROW(INDIRECT(LEN(A2)&":1"))*(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)="+");
(LEN(A2)-LEN(SUBSTITUTE(A2;"+";"")))+1-ROW(INDIRECT("2:"&(LEN(A2)-LEN(SUBSTITUTE(A2;"+";"")))))+1)))

HTH
Ola

[shg]

If you wanted that approach, you could do this:

• Select cell B2
  
• Do Insert > Name > Define, Eval Refers to: = Evaluate(A2) (NB: no dollar signs)
  
• The formula =Eval evaluates a well-formed text formula in the cell to its left. So enter it in B2 to see the result. (The trailing plus sign in olasa's example has to go.)

Please Login or Register  to view this content.

[olasa]

Shg, slightly better ;-)
Lucas1234, what shg is proposing is much better! It will handle both + and - numbers and formulas i.e. "=AVERAGE(100,-50)"

Actually there are two options:
Alt.1: Use the =EVAL Defined Name. In short do what shg explained. Here is an article in Ozgrid's Newsletter.
Alt.2: Use the =EVAL() Formula. But it needs to be installed, since it's an add-in.
It comes from the Morefunc add-in and is one of the most known free add-in to Excel.

Hope this helped
Ola