Bold while adding text to excel in vba

[vmjamshad]

Is there a way where I can bold some of the words of a sentence.

Sheet1.Cells(1, 1) = "Name:" & "John" & "Place:" & "UK" & "Age: " & 18

I want to bold 'Name,'Place','Age' etc. and then add to the excel
Thanks, J

[Paul_Hossler]

Wasn't clear if you wanted the make the literal "Name" bold or the value "John" bold

This makes the literal bold, but you can adjust it if necessary

Option Explicit

Sub test()
    Sheet1.Cells(1, 1).Value = "Name: " & "John" & " Place: " & "UK" & " Age: " & 18
    
    
    Call pvtMakeBold(Sheet1.Cells(1, 1), "Name")
    Call pvtMakeBold(Sheet1.Cells(1, 1), "Place")
    Call pvtMakeBold(Sheet1.Cells(1, 1), "Age")
End Sub

Private Sub pvtMakeBold(R As Range, S As String)
    Dim i As Long
    i = InStr(R.Value, S)
    
    If i = 0 Then Exit Sub
    
    R.Characters(i, Len(S)).Font.Bold = True
End Sub

[georgiboy]

Thought i would post this just for reference as Paul's code looks to be smarter.

This is what I came up with:

Sub Bolder()    
    Dim str As String
    Dim nameX As Long
    Dim placeX As Long
    Dim ageX As Long


    str = "Name: " & "John" & " Place: " & "UK" & " Age: " & 18
    
    nameX = Application.Find("Name:", str)
    placeX = Application.Find("Place:", str)
    ageX = Application.Find("Age:", str)
    
    With Sheet1.Range("A1")
        .Value = str
        .Font.Bold = True
        .Characters(nameX, 5).Font.FontStyle = "Regular"
        .Characters(placeX, 6).Font.FontStyle = "Regular"
        .Characters(ageX, 4).Font.FontStyle = "Regular"
    End With
    
End Sub

Same as Paul, I did not know which part was to be bold.

Hope this helps

[Hightree]

Perhaps something like this macro:

Sub vetje2()
'
' vetje2 Macro
'
A$ = "Name: Charles Age: 18 Ge: men"
'
Range("B1").Select
ActiveCell.FormulaR1C1 = A$
With ActiveCell.Characters(Start:=1, Length:=0).Font
.FontStyle = "Standaard"
End With
With ActiveCell.Characters(Start:=1, Length:=6).Font
.FontStyle = "Vet"
End With
With ActiveCell.Characters(Start:=7, Length:=8).Font
.FontStyle = "Standaard"
End With
With ActiveCell.Characters(Start:=15, Length:=5).Font
.FontStyle = "Vet"
End With
With ActiveCell.Characters(Start:=20, Length:=3).Font
.FontStyle = "Standaard"
End With
With ActiveCell.Characters(Start:=23, Length:=3).Font
.FontStyle = "Vet"
End With
With ActiveCell.Characters(Start:=26, Length:=4).Font
.FontStyle = "Standaard"
End With
Range("B2").Select
End Sub

[Hightree]

This one is nicer:

Sub vetje2()
'
' vetje2 Macro
'
A$ = "Name: Charles Age: 18 Ge: men"
'
'ActiveCell.FormulaR1C1 = "Name: Charles Age: 18 Ge: men"
Range("B1").Select
'ActiveCell.FormulaR1C1 = "Name: Charles Age: 18 Ge: men"
ActiveCell.FormulaR1C1 = A$
With ActiveCell.Characters(Start:=1, Length:=0).Font
.FontStyle = "Standaard"
End With
With ActiveCell.Characters(Start:=1, Length:=6).Font
.FontStyle = "Vet"
.ColorIndex = 3
End With
With ActiveCell.Characters(Start:=7, Length:=8).Font
.FontStyle = "Standaard"
.ColorIndex = 1
End With
With ActiveCell.Characters(Start:=15, Length:=5).Font
.FontStyle = "Vet"
.ColorIndex = 3
End With
With ActiveCell.Characters(Start:=20, Length:=3).Font
.FontStyle = "Standaard"
.ColorIndex = 1
End With
With ActiveCell.Characters(Start:=23, Length:=3).Font
.FontStyle = "Vet"
.ColorIndex = 3
End With
With ActiveCell.Characters(Start:=26, Length:=4).Font
.FontStyle = "Standaard"
.ColorIndex = 0
End With
Range("B2").Select
End Sub

[vmjamshad]

Thanks Paul_Hossler & georgiboy!

I was looking for only the Headings bold (Name:, Please:, Age: ) and keep other regular.
So I just made some changes:

With Sheet1.Range("B1")
    
        .Value = str
        .Font.Bold = False
        .Characters(nameX, 5).Font.Bold = True
        .Characters(placeX, 6).Font.Bold = True
        .Characters(ageX, 4).Font.Bold = True


    End With