Worksheet Event - update chart axis scale, depending on cell value

Hi

On a sheet I have a simple pivot table with Year (Row), £ Sales (Values), and Product Type (Report Filter).

I then have a Line chart running off this.


I'm trying to write a code so when the Product Type filter changes, the chart axis scales update depending on a Cell (C1) which simple diplays which Product is selected.
Basically, I want it to also be 'Auto' unless "All Hard Goods" are selected, whereby I want the scale to be Min: 2000000 and Max: 3000000.

I'm fairly new to vba, and so far I've tried these, with no luck:


This doesn't do what I need:

Please Login or Register  to view this content.

And this comes up with Compile Error: Object Required:

Please Login or Register  to view this content.

Any help would be most appreciated. Thanks

Thanks Izandol
I guess you mean on the worksheet_PivotTableUpdate version?

I've tried that, but now it says 'Run time error 9: Subscript out of range?

Ah yes! Its called "Sheet1".

Thanks you! Works a treat