Lock Height and Color of stacked Bars of Chart with Rows

[caabdul]

Hi

I am stuck in 2 issues.

1-
I have a chart that needs to be printed with bars EXACTLY in front of related data rows. The chart needs to be manually resized to get the bars in exact line with related data Rows. Is there some automatic way (macro or otherwise) so when the chart is created it should have bars in exact line with related data rows?

2-
My data has Headings and sub-heads. Is it possible that chart bars for Headings are a bit different than bar of sub-heads? Difference can be color difference or it can be thickness, shape etc difference.

File is attached

[caabdul]

So seems above things impossible in excel?

[Andy Pope]

It's not impossible but you would need to make some adjustment to the cells.

First problem is category spacing in chart is equal. Whereas your row heights are not, so make them all the same.

If it's one chart then set the fill to none and align by manual adjustment.

If multiple charts you could then use VBA to move the plot area top and height properties. The complicated thing is you actually want to align the plotarea InsideTop and InsideHeight properties but these are read only.
So you would need to adjust the properties you can change, Top and Height, but check the InsideTop and InsideHeight values.

As for formatting category labels, the formatting of the axis is applied to all items.
You could fake the axis labels using extra series.
http://andypope.info/charts/hiliteaxislabels.htm

[caabdul]

Sorry, unfortunately I have different row sizes