VBA Concatenate and retain color format

Bgreen83

New Member
Joined
Oct 3, 2019
Messages
3
Hello Fellow Users,

I need help concatenating two columns and retaining the color format located in column B. Please see the snapshot below. Can someone help me with the VBA code? Thank you!

Column A (A1:A200)
Name

Column B (B1:B200).....Column B text color: RGB (47, 117, 181)
Lname

Column C (C1:C200)
Name - Lname
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,311
Office Version
2013
Platform
Windows
Sub t()
Dim i As Long
For i = 1 To 200
With ActiveSheet
.Cells(i, 3) = .Cells(i, 1).Value & "-" & .Cells(i, 2).Value
End With
Next
ActiveSheet.Range("C1:C200").Interior.Color = RGB(472, 117, 181)
End Sub
 

Bgreen83

New Member
Joined
Oct 3, 2019
Messages
3
Hi JLGWhiz,
Thank you for your reply! This is very helpful. What I would like to do is keep the 'Column A' text font as Black RGB(0, 0, 0) and 'Column B' text font as RGB(472, 117, 181). What would this change look like?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,311
Office Version
2013
Platform
Windows
Sorry, I misread the OP. I assume you mean that in column C you want the font to retain the color of their source. See it this is better.
Code:
Sub t()
Dim i As Long
    For i = 1 To 200
        With ActiveSheet
            .Cells(i, 3) = .Cells(i, 1).Value & "-" & .Cells(i, 2).Value
            .Cells(i, 3).Characters(InStr(.Cells(i, 3).Value, "-") + 1, _
             Len(.Cells(i, 3).Value) - InStr(.Cells(i, 3).Value, "-")).Font.Color = RGB(472, 117, 181)
        End With
    Next
End Sub
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top