Partial colour text with Excel Formula?

[kgkev]

I am dragging some data from various sheets and presenting it on a single line

=IFERROR(VLOOKUP(H5&"|"&B5&"|"&D5,comments!$A:$B,2,FALSE)&" ("&TEXT(((VLOOKUP(H5&"|"&B5&"|"&D5,comments!$A:$D,4,FALSE))),"dd/mm/yyyy")&")",Z5)&IF(AE5<>""," //-// Picking info: "&TEXT(AE5,"dd/mm/yy")&" "&AF5,"")

If the last part contains some text I would like it to be coloured red

=IFERROR(VLOOKUP(H5&"|"&B5&"|"&D5,comments!$A:$B,2,FALSE)&" ("&TEXT(((VLOOKUP(H5&"|"&B5&"|"&D5,comments!$A:$D,4,FALSE))),"dd/mm/yyyy")&")",Z5)[COLOR=#ff0000]&IF(AE5<>""," //-// Picking info: "&TEXT(AE5,"dd/mm/yy")&" "&AF5,"")[/COLOR]

is that possible?

 

[Kamolga]

I did my test by typing for example

sdfwerf //-// Picking info: wef

and it worked but I now realise that if I type

=if(c5<>"",c5&"//-// Picking info:"&C5)

then it does not work.

Of course copy-paste value will solve the problem but you end up with a static cell.

I tried to replace

cell.value

by

application.evaluate(cell.formula)

but without success

 

[shg]

A cell cannot contain both a formula and character-level formatting.

 

[Kamolga]

A cell cannot contain both a formula and character-level formatting.

Thanks, that makes sens.

So calculating in VBA or copy-paste value are the only options