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.
Combine text out of 2 columns and keep formatting
-
- NewLounger
- Posts: 2
- Joined: 17 Oct 2020, 15:07
Combine text out of 2 columns and keep formatting
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 12604
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Combine text out of 2 columns and keep formatting
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
-
- NewLounger
- Posts: 2
- Joined: 17 Oct 2020, 15:07
Re: Combine text out of 2 columns and keep formatting
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:
Thanks again.
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
Last edited by Dedix on 17 Oct 2020, 18:02, edited 1 time in total.
-
- Administrator
- Posts: 12604
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Combine text out of 2 columns and keep formatting
Change
To
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
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
-
- NewLounger
- Posts: 2
- Joined: 17 Oct 2020, 15:07
Re: Combine text out of 2 columns and keep formatting
Hi Stuart,
Many thanks again, it's working.
Many thanks again, it's working.