How do i get the Y axis min and max to be dynamic

[Rednecknerd]

I need my chart to change the min and max values dynamically.
I have data that goes out of the range of my chart so i can not use the auto in axis options.

I have inserted this code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$G$13"
        ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue) _
            .MaximumScale = Target.Value
    Case "$F$13"
        ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue) _
            .MinimumScale = Target.Value
    Case "$H$13"
        ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue) _
            .MajorUnit = Target.Value
    Case Else
End Select
End Sub 

It works if i manually change it but not when it is automatically updated by changing the pn in the dropdown menu on d1
This is for an automated system so needs to work without user input.

Thanks in advance for any input.
I have gotten this far using this forums help and user input.

[Kaper]

You probably gave too little detail to have your problem properly addressed.
Here is my guess:
Formulae in G1 and H1 show what you really would like to have as Min and Max for your axis - it is only my guess - you haven't said it.
No guesses how MajorUnit shall be determined. (may be it is enough to change only max and min, and have majorunit automatic?)

What event shall be "trigger" for the update - here you gave good suggestion:
"automatically updated by changing the pn in the dropdown menu on d1"

Try the folowing code instead (not together with) previous:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
 Case "$D$1"
   ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue) _
     .MaximumScale = Range("H1").Value
   ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue) _
     .MinimumScale = Range("G1").Value
'   ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue) _
     .MajorUnit = Target.Value
End Select
End Sub

As you can see i left the majorunit in the procedure - just commented it out. Once you decide where the source of it is - you can uncomment this line and put the source instead of Target.Value