Formatting text in a formula

[kualjo]

I would like to be able to format some text within a formula that includes both text and numeric values. The formula looks like this:

="** The YTD attainment variance is highlighted if it is greater than " & TEXT(Q51,"##.0%") & " or less than " & TEXT(Q51,"##.0%") & " of the weighted 5-year average YTD attainment for all AGCO tractors and the previous year's attainment was not equal to 0."

I want to be able to Bold only the "YTD attainment variance" part of the sentence. Is there a way to do this?

Appreciate the help!

[mvidas]

kualjo,

If you want to retain the formula in the cell, you cannot bold any text in it. The only way to get around that would be to convert the cell to values then bold that part of the sentence, but it doesnt sound like you would want to do that. It would be possible to use VBA to do this, but the cell wouldnt actually contain a 'real' formula. Would this be an option for you?

Matt

[feathers212]

If this is being written to a cell, try:
ActiveCell.Characters(Start:=8, Length:=24).Font.FontStyle = "Bold"

[mvidas]

If VBA would be an option for you, right-click the tab that you have this on, go to View Code, and in the code pane there paste in the following:Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Q51")) Is Nothing Then Exit Sub
Dim vText As String
With Range("A1") 'cell with the formula
vText = "** The YTD attainment variance is highlighted if it is greater than " & _
Format(Range("Q51").Value, "##.0%") & " or less than " & Format(Range("Q51") _
.Value, "##.0%") & " of the weighted 5-year average YTD attainment for all " & _
"AGCO tractors and the previous year's attainment was not equal to 0."
.Value = vText
.Characters(Start:=1, Length:=7).Font.FontStyle = "Regular"
.Characters(Start:=8, Length:=23).Font.FontStyle = "Bold"
.Characters(Start:=31, Length:=Len(vText) - 30).Font.FontStyle = "Regular"
End With
End Sub
Change the Range("A1") as needed to fit the cell you want the text in. Anytime Q51 changes, the text in that cell will change as well to suit, with the bolded text you want.

Matt

[kualjo]

This may have been more of an Excel question than a VBA question. I was thinking that I could Bold text in the same way that I formatted the number in the "TEXT(Q51,"##.0%")" part of the formula, something like "TEXT(Q51, Bold)" or something like that. It's not critical that I bold this, just thought it would add something to the visual impact of the spreadsheet.
Thanks for the efforts, everyone!