conditional formatting chart data table

[mss5603]

Hello all,

I am trying to set conditional formats to the data table portion of my chart with no success. Under the graph (Plot Area) is the data table portion of the chart where I need to set up some conditional formatting. I need to color the cells according to what their number is. The way I have been getting around this is creating a text box, color fill it, then paste it over the cell in the data table. Doing some research I found that excel is very limited when it comes to formating the data table portion of a chart. Is there any way around this like a VB script? Hope I explained it well and thanks for any help. I am using excel 2003.

[mss5603]

Forgot to attach an example

[Andy Pope]

I could not see the coloured textboxes in your example.

As you same the formatting is extremely limited and coding does not offer any more options when it comes to coding the table.

If possible use the cells below the chart to mimic the table.

[mss5603]

I could not see the coloured textboxes in your example.

Sorry about that, I forgot to add them to the example. I attached a new version with the colored text boxes.

If possible use the cells below the chart to mimic the table.

I was wondering if there was anyway to automate this. We produce a lot of these charts a month and my boss wants this to be automated. Is there anyway to add conditional formatting to the data table section through Visual Basic?

[Andy Pope]

I strongly suggest you look at using cells to provide the data table.

Here is some code that will attempt to position a shape.

As I said there is not hidden formatting available through code.

The data table has no size properties that you can read in order to know it's width or height etc. You can make guesses using other objects but this is not reliable.
If you change the x axis labels to be full month names the height of the axis labels will double with no way of determining this change via code.

[mss5603]

Thanks Andy, thanks for all of your help!