CONCATENATE different fonts in 1 cell

[InfinityMrsn]

Hi Everybody,

I am looking for a VBA code that can help me put 2 or 3 cells with different fonts in 1 cell. Tried many codes, but not what i'm looking for.

I would like in 1 cell a barcode (font I already have) with below it the description in text.

In column A I have the barcode (is a font), column B the article code and column C a location.

In cell D I managed to merge them with a formula, but the Font is forgotten. The result I am looking for is Cell E.

Hope somebody can help me.

Best regards,
Vanity

 

[InfinityMrsn]

The font for barcode is "Code 128" size 48

 

[Georgiboy]

Maybe the below VBA will help:

Sub test()
    Dim rCell As Range  ' Declares a variable to represent each cell in the range
    
    For Each rCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Cells
        ' Loop through each cell in the range
        With rCell
            .Offset(, 3) = Join(Array(.Value, .Offset(, 1).Value, .Offset(, 2).Value), vbNewLine)
            ' Concatenates the values of current cell, cell to the right, and cell two columns to the right
            ' The concatenated values are placed in the cell that is three columns to the right of the current cell
            With .Offset(, 3).Characters(1, Len(.Value))
                ' Targets the first line of the concatenated text within the cell
                .Font.Name = "Code 128"  ' Sets the font name to "Code 128"
                .Font.Size = 48  ' Sets the font size to 48 points
            End With
            .Offset(, 3).HorizontalAlignment = xlCenter  ' Aligns the cell contents horizontally to the center
        End With
    Next rCell
End Sub

I ran it on the below data (Barcode fornt not supported on XL2BB):

Book1
	A	B	C	D
1	Hello World	Hello World	1.2.3	Hello World Hello World 1.2.3
2	Hello World	Hello World	1.2.4	Hello World Hello World 1.2.4
Sheet1

Cell Formulas
Range	Formula
A1:A2	A1	=B1

 

[InfinityMrsn]

Hi Georgiboy ,
This works perfectly. I'm so thankfull.
Many, many, many thanks!

 

[Georgiboy]

You're welcome