0

I found the following code (in the office docs) which allows me to change the axis colour of a group of sparklines:

Sub AxisColor()
    'The sparkline group
    Dim oSparkGroup As SparklineGroup
    'Loop through the sparkline groups on the sheet
    For Each oSparkGroup In Range("A2:C2").SparklineGroups
        'Show the axis
        oSparkGroup.Axes.Horizontal.Axis.Visible = True
        'Set the color of the axis to the color of cell A8
        oSparkGroup.Axes.Horizontal.Axis.Color.Color = Range("A8").Interior.Color
    Next oSparkGroup
End Sub

However I have a (single) group of 60 sparklines and the axis is going to either be above or below the line (it differs case by case). I want to have different colours for an axis that is above all the data (i.e. a max) and for an axis that is below all the data (i.e. a min). Is this possible?

I suspect that I might need to break the sparkline group into 60 individual groups. If someone can show me how to do that (via VBA or otherwise) then I can easily adapt the code above for my purposes.

Improve this question
10
  • I've inspected the objects in the above code in the watch window while debugging and found no way to alter individual item properties. And I've search for documentation about splitting the groups and found nothing.
    – Dan
    Commented Feb 10, 2015 at 6:57
  • @Raystafarian no you can't change the axis colour manually unfortunately. You can't even change them as a group manually, which is why the docs provide the vba code linked to.
    – Dan
    Commented Feb 10, 2015 at 9:29
  • I don't understand what you want, you're already addressing each group. Throw an if in before setting the color if you want a different color depending on the location.
    – Raystafarian
    Commented Feb 10, 2015 at 10:35
  • I only have one group, in this group there are multiple spark lines (i.e. many cells). I want to change the colour of the axis for individual sparklines with-in this one group. If you can help me break this one group into one group per sparkline, then yes I can use an if statement to achieve what I want.
    – Dan
    Commented Feb 10, 2015 at 10:40
  • You are addressing each group For Each oSparkGroup In Range("A2:C2").SparklineGroups
    – Raystafarian
    Commented Feb 10, 2015 at 10:50

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .