VBA Format Data Point Graphics

Topic

New Reply

I am using automation to send data from Access to Excel to populate various simple bar chart graphs. The X axis is set as various Departments using either a two or three digit abbreviation.

Users want to be able to set a specific color by Department or better yet, have the ability to change the color by Dept and have it applied to all of the graphs.

I have been able to change various parts of the graphs using vba code form the data series, labels, and other parameters, but for the life of me, can’t figure out how to change the darn colors on the bars themselves.

Using code similar to the following I figured I could get the color to change but it will not work. Any ideas – Since the departments are always in the same order, Pont 4 for this example should be red.

Xlapp.ActiveSheet.ChartObjects(10).activate
… doing various formatting on chart 10
XlApp.ActiveChart.SeriesCollection(1).Points(4).Interior.ColorIndex = 255 ‘(or VbRed or any other color fails)
… end statement

Reply | Quote

Replies

You are using Interior.ColorIndex

These Run from 0 to 56 only

If you want to use 255 or vbRed etc, then you need to use Interior.Color

So
Interior.Color=vbRed
Is Normally the same as
Interior.ColorIndex=3 on a default Palette (fair point rory)

Reply | Quote

So
Interior.Color=vbRed
Is the same as
Interior.ColorIndex=3

By default that is true, but you can customise a workbook’s colour palette, so colorindex 3 is not necessarily red.
Also be aware that if you specify a value for the Color property that does not match one of the 56 available colours (in Excel 2003 and earlier), you will get the nearest match from the palette, not the exact colour you wanted.

Reply | Quote

You are using Interior.ColorIndex

These Run from 0 to 56 only

If you want to use 255 or vbRed etc, then you need to use Interior.Color

So
Interior.Color=vbRed
Is the same as
Interior.ColorIndex=3

Thanks for the help. Changing to the 56 colors worked. The code was a pain but it is done.

Reply | Quote