Change text colour when using concatenate and TEXT function

[mr_hiboy]

Hi,


I've got two values in a cell, one value is a %age of budget the other variance in £s. I'd like to be able to have green for positive and red for negative, formula is as follow:


=TEXT(VLOOKUP($B$2,Data!$B$9:$DA$32,2,FALSE),"0%")&" / "&TEXT(VLOOKUP($B$2,Data!$B$9:$DA$32,19,FALSE),"0%")


I know i could split it out into 2 or 3 cells but that would mean a lot of reworking!


thanks in advance


Paul

 

[oldchippy]

Hi,


If you are saying something like this 10% / -2%, then I don't think that possible, BUT maybe if you just coloured the cells green by default to assume all the figure are positive, then if there is a negative % then use CF with this


=FIND("-",A1) then format the fill to RED

 

[mr_hiboy]

How do, actually that would cover a few of the cells, I have one that can have be +/-, so I've split the cells!


Cheers

Paul

 

[SirJB7]

Hi, mr_hiboy!


Following oldchippy's idea you could try this:

- cell is green by default

- CF1: =SIGN(IFERROR(FIND("-",A1),0))+SIGN(IFERROR(FIND("+",A1),0))=2 and format for yellow

- CF2: =SIGN(IFERROR(FIND("-",A1),0))=1 and format for red


And you don't have to split the cells.


Regards!