|Since it appears that you are using Excel 2003, I'm pretty confident in saying that you can't do it with a formula.|
There are no built-in functions in Excel 2003 that will change the color of some of the text in a cell. I think you are going to need a macro.
I just tried a few things and the normal way of using VBA to change the font color of text in a cell doesn't change the color of text returned by a formula. The contents of the cell has to be the actual text.
That probably means that you'll have to do the VLOOKUP in VBA and place the results, as text, in the cell and then change the font color with code.
This worked for me, coloring the text after the space red.
'Concatenate VLOOKUP1 & Space & VLOOKUP2
Range("E1") = Application.WorksheetFunction.VLookup(Range("F1"), _
Range("A1:B3"), 2, 0) _
& " " _
& Application.WorksheetFunction.VLookup(Range("G1"), _
Range("C1:D3"), 2, 0)
'Find the space and change the text after the space to red
Characters(Start:=Application.WorksheetFunction.Find(" ", _
Range("E1")) + 1, Length:=256).Font.ColorIndex = 3