Link to home
Start Free TrialLog in
Avatar of ssblue
ssblueFlag for United States of America

asked on

Excel change font color in formula

I need the following formula to show in RED if it is a negative number.

="Budget Variance For Capital Project = "&TEXT('SSG 2017 Orders'!AY2,"[$$ -409]#,##0.00")
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Try below:
="Budget Variance For Capital Project = "&TEXT('SSG 2017 Orders'!AY2,"#,##0_ ;[Red]-#,##0 ")

Open in new window

Avatar of ssblue

ASKER

Nope and it took away the dollar sign.
Try below:
="Budget Variance For Capital Project = "&TEXT('SSG 2017 Orders'!AY2,"[$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00 ")

Open in new window

Avatar of ssblue

ASKER

Sorry, got the dollar sign back but no red font.
OK try this:
="Budget Variance For Capital Project = "&TEXT('SSG 2017 Orders'!AY2,"$ #,##0.00;[Red]-$ #,##0.00")
Avatar of ssblue

ASKER

Still not turning red????
Do not use text function, just use your normal formula:
="Budget Variance For Capital Project = "&'SSG 2017 Orders'!AY2
Then Format Cell to [$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00
Avatar of ssblue

ASKER

That's not working either.
Other way would be;
Using your original formula:
="Budget Variance For Capital Project = "&TEXT('SSG 2017 Orders'!AY2,"[$$ -409]#,##0.00_ ;[Red]-[$$ -409]#,##0.00")
Conditional Formatting as below using formula =Left(H10,1)="-" then format as red font
User generated image
Of course you need to change H10 as per your cell, where you are having the formula
Avatar of ssblue

ASKER

Still not working for me. Not sure what is going on??
Can you upload your file? or send me in pm
Avatar of ssblue

ASKER

it works if I take out the text part of the formula.
Finally. Thank God.
That what I proposed earlier:
Do not use text function, just use your normal formula:
="Budget Variance For Capital Project = "&'SSG 2017 Orders'!AY2
Then Format Cell to [$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00
Avatar of ssblue

ASKER

That doesn't work.  The only time I get RED is when there is NO text involved.
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ssblue

ASKER

Got it!  :)  Thanks!
You're Welcome ssblue! Glad eventually I was able to help :)
The problem was you were using ="Budget Variance For Capital Project = " at the beginning. Format Cell was not treating it as negative.