Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding individual characters in Excel | Excel Discussion (Misc queries) | |||
Preserve cell formatting as SSN | Excel Discussion (Misc queries) | |||
Combine cells with text formatting | Excel Worksheet Functions | |||
combine 2 cells but keep formating of each individual cell | Excel Worksheet Functions | |||
How to preserve formatting with vlookup | Excel Discussion (Misc queries) |