Contatenate different color texts

After looking at a lot of threads, I find it hard to believe that this cannot be done in Excel or VBA. However, I have seen the comments that say that concatenate only works on values. I have to ask anyway.

I want to CONCATENATE(SetColor(A1,red),SetColor(B1,blue)), where SetColor is a function to set the color of that part of the concatenation. Else, if I set the font colors of columns A and B, that the concatenation in column C would retain the colors of A and B.

Believe it. Functions don't do formatting...

This can be done using an event macro. Put something like this in your
worksheet code module:

Private Sub Worksheet_Calculate()
Dim nFirstCellChars As Long
On Error GoTo ErrHandler
Application.EnableEvents = False
With Range("A1:C1")
.Item(3).Value = .Item(1).Text & .Item(2).Text
nFirstCellChars = Len(.Item(1).Text)
.Item(3).Characters(1, nFirstCellChars).Font.ColorIndex = _
IIf(.Item(1).Font.ColorIndex = xlColorIndexAutomatic, _
3, .Item(1).Font.ColorIndex)
.Item(3).Characters(nFirstCellChars + 1).Font.ColorIndex = _
IIf(.Item(2).Font.ColorIndex = xlColorIndexAutomatic, _
5, .Item(2).Font.ColorIndex)
End With
ErrHandler:
Application.EnableEvents = True
End Sub

The downside of this is that changing the font color of a cell doesn't
fire an event so you'd need to recalc the sheet in order for changes to
take place.



In article <[email protected]>,
1scant <[email protected]> wrote:

> After looking at a lot of threads, I find it hard to believe that this
> cannot be done in Excel or VBA. However, I have seen the comments that
> say that concatenate only works on values. I have to ask
> anyway.
>
> I want to CONCATENATE(SetColor(A1,red),SetColor(B1,blue)), where
> SetColor is a function to set the color of that part of the
> concatenation. Else, if I set the font colors of columns A and B, that
> the concatenation in column C would retain the colors of A and B.

Thanks for the reply. I'm not sure how to apply it, though. Where do I put this code?

I hope that there would be enough interest that Microsoft would provide functions in future releases of Excel that would modify attributes such as this.

Put it in the worksheet code module - right-click the worksheet tab and
choose View Code.

In article <[email protected]>,
1scant <[email protected]> wrote:

> Thanks for the reply. I'm not sure how to apply it, though. Where do I
> put this code?