VBA Concatenate and retain color format

[Bgreen83]

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]

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]

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]

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.

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

 

[Bgreen83]

Yes, this one worked great. Thank you so much for your help on this!

 

[JLGWhiz]

Yes, this one worked great. Thank you so much for your help on this!

You're welcome,
regards, JLG