How to change the font of a specific formula which is part of an Excel Cell?

Question

="InterContinental Vienna @ "&M9&" "&M42&" p/room p/night"

based on the above example, M9 = EUR and M42 = 150

Result I am looking for is:

InterContinental Vienna @EUR 150p/room p/night

How do I do this using VBA for each row in a specific column where M9 appears in the cell?

Answer 1

Seems you need a little more VBA, so I have created the code below that incorporates the method described at the link. I do not know how you intend to use this, so the FormatCurrentCellPrice sub is just there as a proxy for your code that will point to the cell you want to format.

The FormatPrice sub determines the location and length of the text you want to bold. It assumes the example you provided where the text is prefixed with '@' and divided by spaces. It also assumes you pass in only a single cell.

Finally, the MakeBold sub is stolen from the other answer to actually set the format for the characters. Since you only want to bold this text, I have removed all the other formatting from the other answer.

Sub FormatCurrentCellPrice()
    FormatPrice Selection
End Sub

Sub FormatPrice(c As Range)
    Dim StartPos As Integer
    Dim EndPos As Integer

    StartPos = InStr(c.Value, "@") + 2
    EndPos = InStr(StartPos, c.Value, " ")
    EndPos = InStr(EndPos + 1, c.Value, " ")
    MakeBold StartPos, EndPos - StartPos
End Sub

Sub MakeBold(StartPos As Integer, charCount As Integer)
    With ActiveCell.Characters(Start:=StartPos, Length:=charCount).Font
        .FontStyle = "Bold"
    End With
End Sub

Answer 2

You can't format specific characters of your cell's text if the text is returned by a formula. You can, however, use VBA's Evaluate function to set the text and then access the Range.Characters collection of the target cell to set specific characters in bold.

Answer 3

Without VBA you can use Absolute References for M9 and M42 like the following:
="InterContinental Vienna @ "&$M$9&" "&$M$42&" p/room p/night"
Format the cell All Bold