I know I can use multiple font colors within a single cell, but is it
possible to use a formula to generate multiple colors within the same
cell?

Excel will accept the following formula, but does not render the
colors:
=TEXT("Red","[red]")&" and "&TEXT("blue","[blue]")

I may be using the wrong function, but I thought I had seen something
like this in the past.

Thanks for your help.
Pete