Colouring text within a formula

Hi,

I could do with your help again with regards to colouring text within a formula (nothing to do with conditional formatting).

In a cell I have...

="INTEGRATED PEST MANAGEMENT "&UPPER(Tab_Name_Accommodation)

which is all in black text.  However, I want the INTEGRATED PEST MANAGEMENT in white text, as this is all on a light blue background.

I have tried using [White] as described for custom formatting with this text but it will not work.  Is there a way of getting text within a formula to be a specific colour, if you are unable to format it in the usual way?

Any help you can give would be appreciated.

Best regards,

Graham

Graham

You cannot pass formatting through a function (formula). It can basically not change any objects.

There are few suitable alternatives. One that springs to mind is to use two cells, one with ="INTEGRATED PEST MANAGEMENT " (Space at end) and another alongside with =UPPER(Tab_Name_Accommodation)

Then colour the cell with =INTEGRATED...... in blue and leave the other on automatic (black). Right align and size the =INTEGRATED.... cell and left align the =UPPER(..... one. Now if you colour the cell interiors (backgrounds). The effect will look like a single cell, like so

Not sure if this helps but the other solutions would be very ugly and involve code.

Hi Nick,

Many thanks for your answer, and this is what I had already thought I would have to do. 

However, if there was a way to do this within a formula, then this would have made it easier, as I have several of these formatting requirements throughout the spreadsheet.

Many thanks for your help & time in this matter.

Best regards,

Graham