="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?

  • It is a little messy, but the method is well described in the second snippet at the following link. stackoverflow.com/questions/1405988/…
    – Code39
    Jun 24, 2016 at 5:22
  • 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"? Jun 24, 2016 at 12:54

3 Answers 3


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

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.


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

  • I tried this but it didn't do the trick... anything I am missing here? Jun 24, 2016 at 12:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .