You need to use somethinglikethis:-


Sub ColorMeInThree()
'Concatenate
[A1].Select
ActiveCell.Value = Sheets("Sheet2").Range("A1") & _
Sheets("Sheet3").Range("A1") & _
Sheets("Sheet4").Range("A1")
'Colors
a = Sheets("Sheet2").Range("A1").Font.ColorIndex
b = Sheets("Sheet3").Range("A1").Font.ColorIndex
c = Sheets("Sheet4").Range("A1").Font.ColorIndex

'Lengths
d = Len(Sheets("Sheet2").Range("A1"))
e = Len(Sheets("Sheet2").Range("A1"))
f = Len(Sheets("Sheet2").Range("A1"))

ActiveCell.Characters(1, d).Font.ColorIndex = a
ActiveCell.Characters(d + 1, d + e).Font.ColorIndex = b
ActiveCell.Characters(d + e + 1, d + e + f).Font.ColorIndex = c

End Sub


http://www.excel-ant.co.uk