Thread: Fonts and Concatenate

So are you saying you're using a Formula to generate the physical Chart Title or are you putting the description into a standard cell ?
Basically speaking it is not possible to use a Formula in a cell and apply different formats to different sections of the cell, in order to do that the cell entry needs essentially to be "value only" (ie no formula)... in general for the value to then be dynamic you would need to use VBA... if you're referring to the Chart Title itself I have even less knowledge as to whether or not you can even use VBA to apply different formats to specific elements of the Title itself... let us know exactly what you're doing and what you're concatenating etc... ie which cells, when/how they alter etc...

Apologies for delay...

I was doing some testing and for whatever reason I'm unable to apply multiple formats to the Chart Title characters via VBA... I've moved this to the Charts Forum - hopefully Andy (Pope) will see this and respond accordingly.

You can not format parts of the result of a formula.

You will need to use VBA to set the text and format the required parts.
In the attached the formula is in C2 with the 3 components in A1:A3

The following located and formats the 2nd and 3rd components.

Code:

Sub ChartTitle()

    Dim strText As String
    Dim lngStart As Long
    Dim lngEnd As Long
    
    With ActiveSheet.ChartObjects(1).Chart
        .HasTitle = True
        .ChartTitle.Text = Range("C2").Value
        .ChartTitle.Font.Size = 20
        
        strText = .ChartTitle.Text
        lngStart = InStr(strText, "Total Variance") + Len("Total Variance") + 1
        lngEnd = InStr(strText, "Percentage")
        .ChartTitle.Characters(lngStart, lngEnd - lngStart).Font.Size = 8
        
        lngStart = lngEnd + Len("Percentage") + 1
        lngEnd = Len(strText)
        .ChartTitle.Characters(lngStart, lngEnd - lngStart + 1).Font.Size = 8
        
    End With
End Sub

Hi Andy, yes, this approach doesn't work for me in XL2007 ... ie the tweaked format is applied to the entire title as opposed to just the sub-selection of characters in the string.... is it a version specific difference or am I being a dunce ?

Will investigate further for '07 solution.

Looks like a bug in xl2007. The formatting is applied to all text in the specified range.

So you will need a embedded textbox and then this code.

Code:

Sub ChartTitle()

    Dim strText As String
    Dim lngStart As Long
    Dim lngEnd As Long
    Dim objTF2 As TextFrame2
    Dim shpTemp As Shape
    
    Set shpTemp = ActiveSheet.ChartObjects(1).Chart.Shapes(1)
    With shpTemp
        Set objTF2 = .TextFrame2
        With objTF2.TextRange
            .Font.Size = 20
            strText = Range("C2").Value
            .Characters.Text = strText
            
            lngStart = InStr(strText, "Total Variance") + Len("Total Variance") + 1
            lngEnd = InStr(strText, "Percentage")
            .Characters(lngStart, lngEnd - lngStart).Font.Size = 8
            
            lngStart = lngEnd + Len("Percentage") + 1
            lngEnd = Len(strText)
            .Characters(lngStart, lngEnd - lngStart + 1).Font.Size = 8
            
        End With
    End With

End Sub

Andy, thanks for that, reassured to know I wasn't being a "muppet"... this time