Combining cells but keeping original font colour

Andy1984

New member
Joined
Aug 15, 2013
Messages
5
Reaction score
0
Points
0
Hi everyone, I am working on an excel workbook that is designed to save time making and printing labels in bulk. NoS on the site has already helped me our hugely with one issue.

I now have an issue with writing colours that I can’t resolve. I need to create a sheet that allows someone to enter the date in blue, the month in green and the year in red in different cells. Easy enough, but I then need to create a way of combining the three cells into one cell, with the writing on different lines, whilst keeping the original colours.

I can combine the cells onto different lines using text wrapping and the Char(10) formula. However, the font colour changes to black.
Any ideas?

2920640_orig.jpg

Many thanks,

Andy
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Don't believe Excel formulas will allow preservation of the text colors as you desire and think that would require VBA.

Did you actually try the formula you show in E3 and get what you show in F3?

If I put what's in F3 into a cell and then run this little macro on it I get pretty much what you're after.

Code:
Sub ChangeColor()

    With ActiveCell
        .Characters(1, 4).Font.Color = vbBlue
        .Characters(6, 6).Font.Color = vbGreen
        .Characters(13, 4).Font.Color = vbRed
    End With

End Sub
 
Top