Maco for displayUnit

[yad]

Hello ,
I would like from my waterfall chart to show the display unit based on the X axis (if it is bigger than 1000000 then Millions , if bigger than 1000 then Thousands ) .

here is my code :
Private Sub Worksheet_Change(ByVal Target As Range)

If Sheets("Sheet2").Range("B5").Value <> 0 Then
Set mychart = Sheets("Sheet2").ChartObjects("Chart 6").Chart
With mychart.Axes(xlValue)

.MinimumScale = 0.9 * Application.WorksheetFunction.Min(Range("b5:b51").Value)

.MaximumScale = 1.1 * Application.WorksheetFunction.Max(Range("b5:b51").Value)




End With

End If

End Sub