Formatting Chart iusing VBA (xlColumnStacked100)

[myoheodu]

I have an Excel worksheet (Sheet1) that contains the following data:

Rule	Total	Met	Not Met	% Full Met	% Not Met
Rule 1	100	56	44	56.00%	44.00%
Rule 2	107	72	35	67.29%	32.71%
Rule 3	79	26	53	32.91%	67.09%
Rule 4	85	38	47	44.71%	55.29%

<tbody>
</tbody>

For each rule (i.e., row) I want to create a chart (stacked % bar). I have created a template to apply the majority of my formatting; however, it’s not giving the same results as when I apply the formatting manually. When I run my macro, as expected, I get 4 charts on Sheet2 that look the same. As an example:

What I need the charts to look like, and what I can easily make them look like manually, is:

I have tried recording these formatting changes but they don’t ‘stick’. Here’s my macro code:

Sub CreateCharts()
'variable declaration
Dim i As Long
Dim iLastRow As Long
Dim chrtMyChart As Chart

'Find the last row that has a rule
iLastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row

'Looping from second row till last row which has the data
For i = 2 To iLastRow
'Sheet 2 is selected bcoz charts will be inserted here
Sheets("Sheet2").Select

'Adds chart to the sheet
Set chrtMyChart = Sheets("Sheet2").Shapes.AddChart.Chart
'sets the chart type
chrtMyChart.ChartType = xlColumnStacked100

'now the line chart is added...setting its data source here
With Sheets("Sheet1")
chrtMyChart.SetSourceData Source:=.Range(.Cells(i, 5), .Cells(i, 6))
End With

'Left & top are used to adjust the position of chart on sheet
chrtMyChart.ChartArea.Left = 1
chrtMyChart.ChartArea.Top = (i - 2) * chrtMyChart.ChartArea.Height

chrtMyChart.ApplyChartTemplate ("DataAnalysisRulesChartTemplate")

chrtMyChart.ChartTitle.Text = Sheets("Sheet1").Cells(i, 1).Value

Next

End Sub

I think that part of the issue is that my chart is based only on two cell values; however, I don't understand why I can apply the formatting manually if that is the case. Thanks in advance for any help you can provide.

M.

Note: I'm unable to paste in images or include attachments. I can email images upon request.