number formatting not responding in formula

[jmartell]

Hi and thanks for the help.

I have a formula that reads: =TEXT(VPS!L23,"$#,###")&" "&TEXT(VPS!N23,"+0.0%;-0.0%") which yeilds $30,563,920 -12.1% in one cell. I can not get the number format to respond to a RED text color for negative numbers. Any thoughts?

Thank you ~

J

 

[Oaktree]

I don't think it's possible to have the red show up via formula.

You'd have to store the value of your formula and use VBA to color the characters. To keep the dynamic functionality of your formulas, you'd need to invoke a worksheet_change event (or similar) to have your code update the values.

Or, you can just deal with black +/- numbers/signs

 

[jmartell]

Thanks for the reply although I do not know VBA that well. Would this be something easy to walk me through?

Thank you~

 

[Oaktree]

See if this thread gets you started...

http://www.mrexcel.com/board2/viewtopic.php?t=117011