BOLDING and UNDERLINING CONCATENATED CELLS

[GNoel]

I need help bolding and underlining cells that are "Concatenated". For example, cell format for cell K12 is already bolded and underlined. If I concatenate (C1,K12,D2) I get a result that doesn't carry over the format of those individual cells - particularly K12 which I want BOLDED and UNDERLINED. Can anyone please help.

 

[zia.butt]

How can I do this if Target Range (A1) is a merged cell ?

Hello, Gnoel,

when using formulas you cannot "divide" the format
using some code you can paste the different values and copy their formats
I did only succeed using a (tiny) space between the values
try this

Option Explicit

Sub test()
Call concatenate_cells_formats(Range("A1"), Range("C1,K12,D2"))
End Sub

Sub concatenate_cells_formats(cell As Range, source As Range)
'Erik Van Geit
'060424

Dim c As Range
Dim i As Integer

i = 1
With cell
.Value = vbNullString
.ClearFormats
    For Each c In source
    .Value = .Value & " " & c
    Next c
.Value = Trim(.Value)
    For Each c In source
        With .Characters(Start:=i, Length:=Len(c)).Font
        .Name = c.Font.Name
        .FontStyle = c.Font.FontStyle
        .Size = c.Font.Size
        .Strikethrough = c.Font.Strikethrough
        .Superscript = c.Font.Superscript
        .Subscript = c.Font.Subscript
        .OutlineFont = c.Font.OutlineFont
        .Shadow = c.Font.Shadow
        .Underline = c.Font.Underline
        .ColorIndex = c.Font.ColorIndex
        End With
        .Characters(Start:=i + Len(c), Length:=1).Font.Size = 1
    i = i + Len(c) + 1
    Next c
End With

End Sub

kind regards,
Erik