combine cells and preserve formatting of individual characters
 

I am trying to create a macro which combines text from multiple cells
into a single cell (simple), but I need to preserve the formatting of
the individual characters. Each cell may contain some characters of
Arial and some characters of Symbol, some itallics, and/or some bold.

How can I manipulate the cell contents without losing the unique
formatting? Is there a way to detect the character formats within a
cell so that I can reapply them after I combine cell values?

Thanks,
Alec



------------------------------------------------
Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/

-- View and post usenet messages directly from http://www.ExcelTip.com
-- Hundreds of free MS Excel tips, tricks and solutions
------------------------------------------------

combine cells and preserve formatting of individual characters
 

I am trying to create a macro which combines text from multiple cells
into a single cell (simple), but I need to preserve the formatting of
the individual characters. Each cell may contain some characters of
Arial and some characters of Symbol, some itallics, and/or some bold.

How can I manipulate the cell contents without losing the unique
formatting? Is there a way to detect the character formats within a
cell so that I can reapply them after I combine cell values?

Check the Help for Characters Object.

Try this, albeit not very tight code since I don't know your application
specifics. I have "hardcoded" 3 cells to sum below, and hardcoded only
the 3 properties you indicated would matter.

Sub foobar()
Dim i as long, i1 as long, i2 as long, i3 as long
i1 = Len(Range("Element1"))
i2 = Len(Range("Element2"))
i3 = Len(Range("Element3"))
Range("Concatenated")=Range("Element1") & Range("Element2") &
Range("Element3")
For i = 1 To i1
Range("Concatenated").Characters(i, 1).Font.name =
Range("Element1").Characters(i, 1).Font.name
Range("Concatenated").Characters(i, 1).Font.Bold =
Range("Element1").Characters(i, 1).Font.Bold
Range("Concatenated").Characters(i, 1).Font.Italic =
Range("Element1").Characters(i, 1).Font.Italic
Next
For i = 1 To i2
Range("Concatenated").Characters(i1 + i, 1).Font.name =
Range("Element2").Characters(i, 1).Font.name
Range("Concatenated").Characters(i1 + i, 1).Font.Bold =
Range("Element2").Characters(i, 1).Font.Bold
Range("Concatenated").Characters(i1 + i, 1).Font.Italic =
Range("Element2").Characters(i, 1).Font.Italic
Next
For i = 1 To i3
Range("Concatenated").Characters(i1 + i2 + i, 1).Font.name =
Range("Element3").Characters(i, 1).Font.name
Range("Concatenated").Characters(i1 + i2 + i, 1).Font.Bold =
Range("Element3").Characters(i, 1).Font.Bold
Range("Concatenated").Characters(i1 + i2 + i, 1).Font.Italic =
Range("Element3").Characters(i, 1).Font.Italic
Next
End Sub

The key here was NOT to set the formula for range Concatenated. Do it
in the code, or else Excel will homogenize the font properties.

You can also use With to improve this if you understand how. If you
want to something REALLY advanced, there are gurus here that can give
you the logic to walk through each and every object property (i.e.
Bold,etc.); I'd stick with doing them one by one if it was me.

Shout back here if you need more.