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

ANSMTH

New Member
Joined
Apr 23, 2015
Messages
3
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,093
Hello and welcome to the Board

red%20numbers.JPG


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.
 
Last edited:

ANSMTH

New Member
Joined
Apr 23, 2015
Messages
3
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,093
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

New Member
Joined
Apr 23, 2015
Messages
3
Ok, great. Do you have some VBA that would get me started to produce the graph I need?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,093
Here is an example:

Hybrids

*AUAVAW
25Q13-3,50%
26Q242%
27Q32,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

labels_neg.JPG


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top