Creating chart by passing sourcedata as variable range

Hi All,

I am trying but not getting the exat result, I want to create a dynamic column chart by passing the ranges as source data which should varry accroding to condition.

for e.g


below given code will create a chart from data A1:E10


With Sheet1.ChartObjects.Add(Left:=10, Width:=500, Top:=70, Height:=275)

.Chart.ChartType = xlColumnClustered
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("'Sheet1'!$Al$1:$E$10")
.Chart.Legend.Select
.Chart.SetElement (msoElementLegendBottom)

End With

I need a Code if range F1 value if 5,

sourcedata shoul be A1:E10

if F1 value change to 4, Source data of chart change to A1:F10

Means F1 Defines the number of columns for chart and source data.

Please help, If it is Possible realy great.


Lots of thanks in advance


Regards,
lavi