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?

Solution

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

Comments

[Code39]

It is a little messy, but the method is well described in the second snippet at the following link. stackoverflow.com/questions/1405988/…

[Metta and Emma Eu]

Thanks Code39 for the pointer... however, the Hotel name will always vary thus the start point will change and the length of the string to format will also change depending on the amount which could be 18 or 1800.... Is there a way to start from the @ and end before "p/room p/night"?

[Metta and Emma Eu]

I tried this but it didn't do the trick... anything I am missing here?