I am trying to get the y-axis to divided by 1000 is c2 has the words Million Barrels per Day. I am not understanding the chart is not changing. Below is my wording. Any help is greatly appreciated!



This is my script:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$c$2" Then

If ActiveCell.Text = "Million Barrels per Day" Then

ActiveSheet.ChartObjects(2).Chart.Axes(xlValue).DisplayUnit = xlThousands
End If
End If
End Sub

It is located in Sheet 2's- worksheet- change environment.