Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. -- 1scant ------------------------------------------------------------------------ 1scant's Profile: http://www.excelforum.com/member.php...o&userid=31635 View this thread: http://www.excelforum.com/showthread...hreadid=515930 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Contatenate different color texts
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 , 1scant 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Contatenate different color texts
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. -- 1scant ------------------------------------------------------------------------ 1scant's Profile: http://www.excelforum.com/member.php...o&userid=31635 View this thread: http://www.excelforum.com/showthread...hreadid=515930 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Contatenate different color texts
Put it in the worksheet code module - right-click the worksheet tab and
choose View Code. In article , 1scant wrote: Thanks for the reply. I'm not sure how to apply it, though. Where do I put this code? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|