Concatenate and set Bold to first string - how to use across more than one row

[Jediben]

Hello everyone, I have encountered the following scenario. I need to concatenate two cells into a third cell while adding a ": " to act as a divider between the two, in columns 1000 cells deep. I also need to make the divider and all text before it BOLD. I have been using the following code from http://www.mrexcel.com/forum/excel-questions/352267-concatenate-bold-excel-2007-a.html:

<colgroup><col width="64" style="width: 48pt;">
<tbody>


</tbody>

Sub BoldPartText()
Dim Part1Len, Part2Len, DividerLen As Integer
Dim Divider As String
Part1Len = Len(Range("F3")) + 1
Part2Len = Len(Range("G3"))
Divider = ": "
DividerLen = Len(Divider)
Range("E3") = Range("F3") & Divider & Range("G3")
With Range("E3").Characters(Start:=1, Length:=Part1Len).Font
.FontStyle = "Bold"
End With
End Sub

<colgroup><col width="64" style="width: 48pt;">
<tbody>


</tbody>

This works fine but it only works on one row (row 3). I am a novice at VBA and I don't understand the change I need to make so that all cells from F3:F1001 and G3:G1001 are concatenated and the output put into cells E3:E1001 with just one use of the macro.

<colgroup><col width="64" style="width: 48pt;">
<tbody>


</tbody>

Can anyone help please? I am on Excel 2010. Thanks.

 

[Jonmo1]

Welcome to the board.

Try

Sub BoldPartText()
Dim Part1Len As Integer, Part2Len As Integer, DividerLen As Integer
Dim Divider As String, c As Range, lr As Long

lr = Cells(Rows.Count, "F").End(xlUp).Row

For Each c In Range("F3:F" & lr)
    Part1Len = Len(c) + 1
    Part2Len = Len(c.Offset(0, 1))
    Divider = ": "
    DividerLen = Len(Divider)
    c.Offset(0, -1) = c & Divider & c.Offset(0, 1)
    With c.Offset(0, -1).Characters(Start:=1, Length:=Part1Len).Font
        .FontStyle = "Bold"
    End With
Next c
End Sub

 

[Jediben]

Jonmo, that is amazing. Thank you so much. I will now go off and try to read up on what your new code has done. I was originally trying to change the Ranges (E3, F3 and G3) by expressing them as F3:F1001 but it was giving an error. How do I 'upvote' your reputation on these boards?

 

[Jonmo1]

You're welcome.

We really don't have a 'reputation' scale on this forum
Giving 'likes' is about as close as it gets.