Combine text out of 2 columns and keep formatting

Dedix
NewLounger
Posts: 2
Joined: 17 Oct 2020, 15:07

Combine text out of 2 columns and keep formatting

Post by Dedix »

Hello,

I'm new on this forum and already saw a bit similar topic, however I could not make it useful for my situation.

What I need is that 2 cells (Cell A2 & B2) will be combined into 1 cell (Cell C2) and the text from Cell "B1" should be on the next row(in the same cell) and needs to be highlighted in red color and bold.

Currently I am combining the cells by a formula (=A1&teken(10)&B1) --> then copy and past as value in Column D, then manually change to red color an bold

Please find attached sample file for references:
Column A = Description
Column B = Remarks on Cell A1
Column C = How it should be after using the Macro
Column D - after paste as value, manually change color and bold format

My knowledge of VBA is not very good, I'm always searching on forums then copy-paste and adjust to my preference however with this one I could not solve it,

Hope someone can help me out?

Many thanks in advance.
You do not have the required permissions to view the files attached to this post.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Combine text out of 2 columns and keep formatting

Post by StuartR »

There are very clever coders here who could do this much more elegantly than me, but this should do what you need.

Code: Select all

Sub MergeAndColor()
    Dim rngDescrip As Range
    Dim rngA As Range
    Set rngDescrip = Range("A2", Range("A65535").End(xlUp))

    For Each rngA In rngDescrip
        rngA.Offset(0, 2).Value = rngA.Value & Chr(10) & rngA.Offset(0, 1).Value
        rngA.Offset(0, 2).Characters(Len(rngA.Value) + 1, Len(rngA.Offset(0, 2).Value) - Len(rngA.Value)).Font.Color = RGB(255, 0, 0)
    Next rngA
End Sub
StuartR


Dedix
NewLounger
Posts: 2
Joined: 17 Oct 2020, 15:07

Re: Combine text out of 2 columns and keep formatting

Post by Dedix »

Good day Stuart,

Many thanks for your swift reply, this is perfect.
I just added the function to make it also bold.

In case there are no remarks (empty cell) then the cell should not be amended.
with this code there is an empty line in the combined cells.

VBA Code including Bold:

Code: Select all

Sub MergeAndColor()
    Dim rngDescrip As Range
    Dim rngA As Range
    Set rngDescrip = Range("A2", Range("A65535").End(xlUp))

    For Each rngA In rngDescrip
        rngA.Offset(0, 2).Value = rngA.Value & Chr(10) & rngA.Offset(0, 1).Value
        rngA.Offset(0, 2).Characters(Len(rngA.Value) + 1, Len(rngA.Offset(0, 2).Value) - Len(rngA.Value)).Font.Color = RGB(255, 0, 0)
        rngA.Offset(0, 2).Characters(Len(rngA.Value) + 1, Len(rngA.Offset(0, 2).Value) - Len(rngA.Value)).Font.Bold = True
    Next rngA
End Sub
Thanks again.
Last edited by Dedix on 17 Oct 2020, 18:02, edited 1 time in total.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Combine text out of 2 columns and keep formatting

Post by StuartR »

Change

Code: Select all

        rngA.Offset(0, 2).Value = rngA.Value & Chr(10) & rngA.Offset(0, 1).Value
        rngA.Offset(0, 2).Characters(Len(rngA.Value) + 1, Len(rngA.Offset(0, 2).Value) - Len(rngA.Value)).Font.Color = RGB(255, 0, 0)
        rngA.Offset(0, 2).Characters(Len(rngA.Value) + 1, Len(rngA.Offset(0, 2).Value) - Len(rngA.Value)).Font.Bold = True
To

Code: Select all

        If rngA.Offset(0,1).Value <> "" Then
                rngA.Offset(0, 2).Value = rngA.Value & Chr(10) & rngA.Offset(0, 1).Value
                rngA.Offset(0, 2).Characters(Len(rngA.Value) + 1, Len(rngA.Offset(0, 2).Value) - Len(rngA.Value)).Font.Color = RGB(255, 0, 0)
                rngA.Offset(0, 2).Characters(Len(rngA.Value) + 1, Len(rngA.Offset(0, 2).Value) - Len(rngA.Value)).Font.Bold = True
        Else
                rngA.Offset(0, 2).Value = rngA.Value
        EndIf
StuartR


Dedix
NewLounger
Posts: 2
Joined: 17 Oct 2020, 15:07

Re: Combine text out of 2 columns and keep formatting

Post by Dedix »

Hi Stuart,

Many thanks again, it's working.