How do I bold a word within a string using VBA-CodePudding

Currently I am writing a macro that autofills different sheets in a workbook from a parent sheet. I have written some vba script to pull through a value that is a date and concatenate that with other text.

Worksheets("sheet name").Range("B24").Value = "Text  <" & Worksheets("sheet name").Range("G17").Value & ">. more text for three years."

I want to bold the word "three". I already recorded a macro to do this however the issue I am running into is the length of the date won't always be the same, it could be 11/22/2022 or 1/22/2022. The value is being placed and pulled from a merged cell.

Sheets("sheet name").Select
Range("B24:E24").Select

ActiveCell.FormulaR1C1 = _
    "Test <" & Worksheets("sheet name").Range("G17").Value & ">. more text for three years."
With ActiveCell.Characters(Start:=1, Length:=146).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=147, Length:=5).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=152, Length:=7).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With

CodePudding user response：

You'll need to use the instr function.

Here's some sample code that you could probably levarage. It's based somewhat on yours. If all you're doing is bold, you don't need all that other stuff you picked up from macro recording.

Sub makePartBold()
Const cellAddress = "B24"
Const textToFind = "three"
Dim startPos As Long
    
    startPos = InStr(1, Range(cellAddress).Value, textToFind, vbTextCompare)

    With Range(cellAddress).Characters(Start:=startPos, Length:=Len(textToFind)).Font
    
        .FontStyle = "Bold"
    End With

End Sub