Concatenate and bold

[AroraMandeep]

Hi,

i am ccocatenating 2 columns that are I and J, and i want the test in column I to be bold.
how can i do it?

 

[Michael M]

Can you give us a bit more information ?
You want the resultant cell to be part Bold and part normal ??

 

[AroraMandeep]

Yes
i want text from column I to be bold and column J to be normal

 

[Yongle]

Amend value of variables to match your requirements

Sub Concat()
    Const resultCol = "[COLOR=#ff0000]K[/COLOR]", fRow = [COLOR=#ff0000]2[/COLOR]
    
    Dim lRow As Long, r As Long, x As Long, ws As Worksheet, iStr As String, jStr As String, cel As Range
    Set ws = [COLOR=#ff0000]ActiveSheet[/COLOR]
    lRow = WorksheetFunction.Max(ws.Range("I" & Rows.Count).End(xlUp).Row, ws.Range("J" & Rows.Count).End(xlUp).Row)
    
    For r = fRow To lRow
        Set cel = ws.Cells(r, resultCol)
        iStr = ws.Cells(r, "I").Value
        jStr = ws.Cells(r, "J").Value
        cel.Value = iStr & jStr
        cel.ClearFormats
        For x = 1 To Len(iStr)
            cel.Characters(x, 1).Font.Bold = True
        Next x
    Next r
End Sub

 

[Sanjeev1976]

Yes
i want text from column I to be bold and column J to be normal

Welcome to the board Mandeep. You can try the following macro to achieve your purpose. Change the Startrow and Endrow values as per your requirement.

Sub Concat_BoldColi()


Startrow = 1
Endrow = 20
ValueColumn = 11
ColBold = 9
ColReg = 10
RowNum = Startrow
Do While RowNum < Endrow
RegValue = Cells(RowNum, ColReg).Value
RegValLength = Len(RegValue)
BoldValue = Cells(RowNum, ColBold).Value
BoldValLength = Len(BoldValue)
Cells(RowNum, ValueColumn) = BoldValue + " " + RegValue
Cells(RowNum, ValueColumn).Select
With ActiveCell.Characters(Start:=1, Length:=BoldValLength).Font
.FontStyle = "Bold"
End With
RowNum = RowNum + 1
Loop


End Sub

 

[AroraMandeep]

hi,

Thank you for the solution, it worked but it is making text form both columns bold. I only wanted text of one column to be bold.

 

[Sanjeev1976]

Delete the existing values in column K and then try the macro again

 

[Yongle]

@AroraMandeep @Sanjeev1976

Delete the existing values in column K and then try the macro again

- that is not correct
- clear formats NOT values (refer to solution in post#4)

Sub Concat_BoldColi()

Startrow = 1
Endrow = 20
ValueColumn = 11
ColBold = 9
ColReg = 10
RowNum = Startrow
Do While RowNum < Endrow
    RegValue = Cells(RowNum, ColReg).Value
    RegValLength = Len(RegValue)
    BoldValue = Cells(RowNum, ColBold).Value
    BoldValLength = Len(BoldValue)
    Cells(RowNum, ValueColumn) = BoldValue + " " + RegValue
    Cells(RowNum, ValueColumn).Select
[COLOR=#ff0000]    ActiveCell.ClearFormats[/COLOR]
    With ActiveCell.Characters(Start:=1, Length:=BoldValLength).Font
        .FontStyle = "Bold"
    End With
    RowNum = RowNum + 1
Loop
End Sub

 

[Sanjeev1976]

Thanks for the correction.