Formatting Chart iusing VBA (xlColumnStacked100)

[myoheodu]

I have an Excel worksheet (Sheet1) that contains the following data:
[TABLE="width: 557"]
<tbody>[TR]
[TD]Rule
[/TD]
[TD]Total
[/TD]
[TD]Met
[/TD]
[TD]Not Met
[/TD]
[TD]% Full Met
[/TD]
[TD]% Not Met
[/TD]
[/TR]
[TR]
[TD]Rule 1
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]56
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]56.00%
[/TD]
[TD="align: right"]44.00%
[/TD]
[/TR]
[TR]
[TD]Rule 2
[/TD]
[TD="align: right"]107
[/TD]
[TD="align: right"]72
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]67.29%
[/TD]
[TD="align: right"]32.71%
[/TD]
[/TR]
[TR]
[TD]Rule 3
[/TD]
[TD="align: right"]79
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]53
[/TD]
[TD="align: right"]32.91%
[/TD]
[TD="align: right"]67.09%
[/TD]
[/TR]
[TR]
[TD]Rule 4
[/TD]
[TD="align: right"]85
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]47
[/TD]
[TD="align: right"]44.71%
[/TD]
[TD="align: right"]55.29%
[/TD]
[/TR]
</tbody>[/TABLE]

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.