format formula help

Microsoft Excel 2003 (full)
July 28, 2009 at 15:28:19
Specs: Windows Vista
I am doing a concatenate function that has 2 vlookups imbedded in it. What I want to be able to do is change the color of the second value that is looked up. So that my concatenated cell shows both values together but each are different colors. Can anyone help me with this?


See More: format formula help

Report •

July 28, 2009 at 17:20:05
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.

Sub ColorPart2()
'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
 Range("E1"). _
   Characters(Start:=Application.WorksheetFunction.Find(" ", _
   Range("E1")) + 1, Length:=256).Font.ColorIndex = 3
End Sub

Report •
Related Solutions

Ask Question