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