Link to home
Start Free TrialLog in
Avatar of looper8
looper8

asked on

Setting colours on Excel Chart

Making some Excel charts from an Access database.  Using the following code to change the colours of chart bars etc

xlBook.ActiveChart.SeriesCollection(1).Points(k).Interior.ColorIndex = Series1Colour
etc etc

Problem: the legend keys on the data table display the default colours and not the ones I've changed the columns to.  How can I chage those legend keys too?
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of looper8
looper8

ASKER

Thanks Wayne.

I think I need to show you more code, as there's a loop which I think is causing problems!  What results is that all the bars except the last one are reset to default Access colours.  I tried pulling the xlSolid line out before the If Not line, but that doesn't help either.  Any ideas?

intNumOfSeries1 = xlBook.ActiveChart.SeriesCollection(1).Points.Count
For k = 1 To intNumOfSeries1
            If Not ChartType = xlLineMarkers Then
                .SeriesCollection(1).Interior.Pattern = xlSolid
                .SeriesCollection(1).Points(k).Interior.ColorIndex = Series1Colour
               
            Else
                With .SeriesCollection(1)
                    .Border.ColorIndex = Series1Colour
                    .MarkerForegroundColorIndex = Series1Colour
                    .MarkerBackgroundColorIndex = Series1Colour
                End With
            End If
        Next
Avatar of looper8
looper8

ASKER

Just noticed that any chart that only has one series is fine (there are three different colour versions) but that any with more than one series have the problems we're looking at.  At the moment I've got one set of the code above to go through each series ... was feeling too lazy to put them into a loop (will do eventually).
Avatar of looper8
looper8

ASKER

Hey Wayne,

Sorted it ...
intNumOfSeries = xlBook.ActiveChart.SeriesCollection.Count
            For i = 1 To intNumOfSeries
                Select Case i
                    Case 1
                        .SeriesCollection(i).Interior.ColorIndex = Series1Colour
                    Case 2
                        .SeriesCollection(i).Interior.ColorIndex = Series2Colour
                    Case 3
                        .SeriesCollection(i).Interior.ColorIndex = Series3Colour
                    Case 4
                        .SeriesCollection(i).Interior.ColorIndex = Series4Colour
                End Select
            .SeriesCollection(i).Interior.Pattern = xlSolid
            Next
Avatar of looper8
looper8

ASKER

Something weird happened there ... don't know how I submitted that last bit ...!

Anyway, I basically did what you said with a loop and all's well.  Thanks!