how to merge two cells and keep the different internal formats

As I said this is not easy and you need a totally different code architecture for that as Vijay's "quick" code.

I don't know if you understand the code below, unfortunately, it takes a lot of experience and understanding how objects and VBA work.

Andreas.

Sub Test()
  Dim Source As Range, Dest As Range, This As Range
  Dim FontProperties, Delimiter, Item, Value
  Dim f As Long, i As Long
  
  'Names of all font properties
  FontProperties = Array("Bold", "Color", "FontStyle", "Italic", "Name", "Size", "Strikethrough", "Subscript", "Superscript", "ThemeColor", "ThemeFont", "TintAndShade", "Underline")

  'Errors off, we handle all by oruself
  On Error Resume Next
  
  'Get the user input
  Set Source = Application.InputBox("Select all cells to combine", "Source", Selection.Address, Type:=8)
  If Source Is Nothing Then Exit Sub
  Do
    Set Dest = Application.InputBox("Select the destination cell", "Dest", Selection.Address, Type:=8)
    If Dest Is Nothing Then Exit Sub

    If Not Intersect(Source, Dest) Is Nothing Then
      MsgBox "The destination cell can not be inside the source cells", vbExclamation, "Error"
    End If
  Loop Until Intersect(Source, Dest) Is Nothing

  Delimiter = Application.InputBox("Optional Delimiter (use ^ for a line break)", "Delimiter", Type:=2)
  If Delimiter = "^" Then Delimiter = vbLf

  'Copy all values into the destination cell
  Dest.Clear
  For Each This In Source
    Dest = Dest & This & Delimiter
  Next
  Dest = Left(Dest, Len(Dest) - Len(Delimiter))

  'Now we have to copy over the font property for each char!
  f = 0
  For Each This In Source
    For i = 1 To This.Characters.Count
      With Dest.Characters(f + i, 1)
        For Each Item In FontProperties
          Value = Null
          Value = CallByName(This.Characters(i, 1).Font, Item, VbGet)
          If Not IsNull(Value) Then
            CallByName .Font, Item, VbLet, Value
          End If
        Next
      End With
    Next
    f = f + Len(This) + Len(Delimiter)
  Next
End Sub

Hi zamek7

Greetings! I am Vijay, an Independent Advisor. You can use below Macro to merge two cells for value and font size (This won't copy any other attribute of cell like color and weight. If they are also needed, they will need to be coded). You have to select 2 cells and run this.

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again. Sub MergeCells() Dim v1, v2, l, size1, size2 Dim Cell As Range If Selection.Count <> 2 Then MsgBox "Select only 2 Cells" Exit Sub End If Set Cell = Selection(1) v1 = Selection(1).Value size1 = Selection(1).Font.Size l = Len(v1) v2 = Selection(2).Value size2 = Selection(2).Font.Size Selection.Merge Cell = v1 & v2 Cell.Characters(Right(l + 1, Len(Cell) - l)).Font.Size = size2 End Sub

Do let me know if you have any more question or require further help.

can you please explain how your code works.

also if I want to run this for x rows how could this routine be put in a loop?

what are the other codes for character attributes like color, underline or bold, that could be used?

where could I look up the references for these codes?

Hi Zamek7, unfortunately when combining two cells, that becomes like one unique cell therefore it will only allow one text font.

Thanks

You can do that only with VBA and even with a macro this in not simple.

Are you looking for a universal solution (using selected cells) or do you have a specific scenario?

Andreas.