Color negative percentage red in a text string

    I have created a text string pulling percentage values from another sheet. Is it possible to color the text red if the percentage is negative?


    ="Societe Generale CTA indices showed that trend followers outperformed short-term traders during the month, with the Trend Index returning " & TEXT('Summary Page'!$R$38,"0.00%") & " and the Short-Term Traders returning " & TEXT('Summary Page'!$R$39,"0.00%") & "."


    Thanks in advance.


    [TABLE="width: 64"]

    [tr]


    [TD="class: xl83, width: 64"]


    [/TD]

    [/tr]


    [/TABLE]

    Re: Color negative percentage red in a text string


    Use conditional formatting. Using "Format only cells that contain" with cell value less than 0
    under format select font then color then red.

    Re: Color negative percentage red in a text string


    Thank you for the reply. That does not work here though because the formula generates the following text string in one cell:


    Societe Generale CTA indices showed that trend followers outperformed short-term traders during the month, with the Trend Index returning -3.39% and the Short-Term Traders returning -0.34%.


    The only portion of text that I want to be red here are the percentages so I'm not sure conditional formatting will work here.

    Re: Color negative percentage red in a text string


    Just to confirm, you cannot use Conditional formatting for part of a cell contents. But you also cannot format individual characters in the result of a formula.


    This can only be done using VBA.

    Re: Color negative percentage red in a text string


    Thank you, I was wondering if it was possible and it is not (easily) so I appreciate the help.

    Re: Color negative percentage red in a text string


    I may be misunderstanding exactly what is going on, but you can color the whole text in the cell if the percent is less than zero. Even if there is a formula in the cell. Admittedly the formula I was using is a bit simpler. the formula in the cell I used was

    Code
    =IF(G5=0,"Societe Generale CTA indices showed that trend followers outperformed short-term traders during the month, with the Trend Index returning -3.39% and the Short-Term Traders returning -0.34%.",0)


    The formula for conditional formatting I used was

    Code
    =LEFT(RIGHT(A2,7),1)="-"


    This turned the whole cell red text. No you cannot change just the color of the number only though.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login