Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    Manchester, Lancashire, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Highligting Axes Labels (XP/SP3)

    Hello, Loungers

    I have a chart showing comparative performance of individuals on a bar chart. I need a way of highlighting groups of individuals. I already have a secondary axis, so I cannot use this method, and I was wondering whether there is a way of colouring a specific x-axis labels using VBA.

    I know I have seen charts that use this method to highlight data, but I am guessing that they were not using Excel.

    Julian

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Highligting Axes Labels (XP/SP3)

    Could you provide some more details?

    As far as I know, If you want to actually change the color of the "axis labels", this can not be done "directly", even with VB for just a few of them.

    You could (manually or with VB) add a text box over some of the labels and have the individual text boxes a different color so it "looks" like some have different colors.

    You could also (depending on need) not use a category label at all, but use a "hidden range" or ranges to add different colored labels depending on some condition. (no macro, just formula and setup)

    I have attached an example. It has names in X and 3 Ys: the score and 2 columns of zeros. the zeroes are both lines and not columns and plot with no line and no colors to the markers (so they are hidden). I added datalabels to them. the value of the label is dependent on the score. scores greate than 90 yield a green label and scores less than 50 yield a red. The "true" X- axis labels are the values in between.

    If you change the values in column C you will the colors of the chart Names change.

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    Manchester, Lancashire, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Highligting Axes Labels (XP/SP3)

    Thank you for the reply and the spreadsheet. Your solution of hiding the original labels is clever, and I had arrived at a similar solution myself, after more playing around. I am also using data labels, aligned at the "Inside Base" position. These also use conditional formatting to change the colour, but I also added conditional text (e.g. [ Blue][=0]"Ok"; so that instead of showing a number, the label shows the text.

    Your spreadsheet gives me some ideas for an enhancement to what I have done, which I will go and try now.

    One problem with my spreadsheet is that is deals with dynamic data ranges, so the size of the chart changes depending on the data. The spreadsheet calculates the range of the chart using MATCH and COUNTIF to determine the range coordinates for the chart, and named ranges linked to INDIRECT functions for the Series formula on the chart. This seems to make a mess of the custom data labels.

    Julian

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Highligting Axes Labels (XP/SP3)

    If you add the labels for the max range you expect, I think you should be fine. If you are still having problems, perhaps you could post an example and explain the issues.

    If nothing else you could have the datalabels assigned with VB, though I prefer the formulaic approach.

    Steve

Posting Permissions

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