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

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,099
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Last edited by Worf; Apr 24th, 2015 at 10:32 PM.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  3. #3
    New Member
    Join Date
    Apr 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!!

  4. #4
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,099
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  5. #5
    New Member
    Join Date
    Apr 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  6. #6
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,099
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

      
    Here is an example:

    Hybrids

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


    Excel tables to the web >> Excel Jeanie HTML 4



    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com