conditionally color format numbers in y-axis title that includes text

[ANSMTH]

The y-axis of my graph is a combination of text and numbers:

(bar) (bar) (bar)
1412 1501 1502

15Q3 Margin:

(3.5%)

Each row of the above set of data is contained in seperate columns in source data. I like the display of the data on the graph axis in this manner, however, I cannot seem to get the graph "format axis" option to recognize the (%) as a number. I assume since there is text, the entire axis is defined as text and it shows exactly as formatted in the source data except for the color. I want any negative margin to show formatted as: 0.0%;[Red] (0.0%). Is there a way to force it to recognize the "( )" to apply the red color? I am not familiar with vb for a graph so if that is the solution please be specific to how to access the graph's code. I sincerely appreciate any help!
Excel 2010

 

[Worf]

Hello and welcome to the Board

If you have numbers as source data, it’s easy to have the formatting as shown above.
If it’s text, see the page below for a possible solution.

Individually Formatted Category Axis Labels - Peltier Tech Blog

If you go for this procedure, I can write VBA code to automatically format each individual label.

 

[ANSMTH]

Thank you for your reply! I did see the page referenced and it's not exactly what I'm needing - I don't think. I want to keep the other text in the axis title 1412 1501 1502 / 15Q3 Margin:) but I want to be able to add the conditionally formatted numbers as well below the "15Q3 Margin:". I wasn't able to create dummy data to populate a secondary horizontal axis label so I could format it as a number.
I am interested in the VB if that is a possible solution.

Thanks so much!!

 

[Worf]

The page describes a solution using dummy series and data labels, which can contain any alphanumeric text and be individually formatted. The formatting can be done automatically with VBA.
It would produce the chart you described.

 

[ANSMTH]

Ok, great. Do you have some VBA that would get me started to produce the graph I need?

 

[Worf]

Here is an example:

Hybrids

*	AU	AV	AW
25	Q1	3	-3,50%
26	Q2	4	2%
27	Q3	2,5	-1%

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sub CustomLabels()
Dim co As ChartObject, dum As Series, i%, dl As DataLabel, tb As Shape, a
Set co = ActiveSheet.ChartObjects("Chart4")
For Each dum In co.Chart.SeriesCollection
    If dum.Name = "Dummy" Then dum.Delete   ' old dummies
Next
Set dum = co.Chart.SeriesCollection.NewSeries
dum.Name = "Dummy"
dum.Values = Array(0, 0, 0)             ' dummy series
dum.XValues = "=Hybrids!$au$25:$au$27"
dum.ChartType = xlLine
dum.HasDataLabels = True
dum.DataLabels.Position = xlLabelPositionAbove
For Each tb In co.Chart.Shapes              ' old text boxes
    tb.Delete
Next
For i = 1 To 3
    dum.Points(i).DataLabel.Text = Cells(24 + i, 47).Value & vbLf & Format(Cells(24 + i, 49).Value, "0.0%")
Next
dum.Format.Line.Visible = msoFalse
co.Chart.Axes(xlCategory, xlPrimary).TickLabelPosition = xlNone
For Each dl In dum.DataLabels
    Set tb = co.Chart.Shapes.AddTextbox(1, dl.Left - 5, dl.Top, dl.Width, dl.Height)
    With tb.TextFrame2
        .WordWrap = msoFalse
        .AutoSize = msoAutoSizeShapeToFitText
        With .TextRange
            .Text = dl.Text
            .Font.Size = dl.Format.TextFrame2.TextRange.Font.Size + 1
            a = Split(.Text, vbLf)
            .ParagraphFormat.Alignment = msoAlignCenter
            If InStr(a(1), "-") > 0 Then                ' negative
                With .Characters(Len(.Text) - Len(a(1)) + 1, Len(a(1))).Font.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(245, 4, 4)
                End With
            End If
        End With
    End With
Next
dum.HasDataLabels = False
End Sub