Private Sub Worksheet script is not effecting worksheet

[Annette Smith]

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.

 

[Dave Peterson]

Text comparisons inside VBA are case-sensitive. "$c$2" is not the the same as
"$C$2".

And since the procedure knows what cell(s) changed, I'd use something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
If ucase(Target.Text) = ucase("Million Barrels per Day") Then
me.ChartObjects(2).Chart.Axes(xlValue).DisplayUnit = xlThousands
End If
End If
End Sub

Me is the object that owns the code. In this case, it's the worksheet. I stay
away from activecell and activesheet whenever possible.