How to set the Y axis using VBA?? Help

[Branagan_1]

Hi,

I am not very good with VBA but, I'm trying to change the Y Axis to a value in a cell (basically i'm trying to tighten up the auto setting). I would also like it to only happen to a specific chart (Chart 45) every time the cell changes. I have managed to find some code that I think may work but I already have a worksheet_change event and I don't know how to combine the two... Code below...Any help would be great... Thanks in advace!

Current code

Private Sub Worksheet_Change(ByVal Target As Range)


  If Target.Column = 5 And Target.Row = 241 Then
    RangeValue = "E233:H233"
    DrivingCell = "E241"
    GoTo FormatChange
  End If
    If Target.Column = 6 And Target.Row = 241 Then
    RangeValue = "K233:N233"
    DrivingCell = "F241"
    GoTo FormatChange
  End If
  If Target.Column = 7 And Target.Row = 241 Then
    RangeValue = "Q233:T233"
    DrivingCell = "G241"
    GoTo FormatChange
  End If
  If Target.Column = 8 And Target.Row = 241 Then
    RangeValue = "W233:Z233"
    DrivingCell = "H241"
    GoTo FormatChange
  End If
Exit Sub

FormatChange:
Application.ScreenUpdating = False
Range(RangeValue).Select
      Select Case Range(DrivingCell).Value
        Case "Vol"
            Selection.NumberFormat = "0,000"
        Case "Vol WSE"
            Selection.NumberFormat = "0,000"
        Case "SOM Share"
            Selection.NumberFormat = "0.0%"
        Case "SOM Share SE"
            Selection.NumberFormat = "0.0%"
        Case "SOM Share WSE"
            Selection.NumberFormat = "0.0%"
    End Select
Range(DrivingCell).Select
Application.ScreenUpdating = True


End Sub

Code I think will work

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$A$1"
        ActiveSheet.ChartObjects("Chart 45").Chart.Axes(xlValue) _
            .MaximumScale = Target.Value
    Case "$A$2"
        ActiveSheet.ChartObjects("Chart 45").Chart.Axes(xlValue) _
            .MinimumScale = Target.Value
    Case "$A$3"
        ActiveSheet.ChartObjects("Chart 45").Chart.Axes(xlValue) _
            .MajorUnit = Target.Value
    Case Else
End Select
End Sub