VBA formatting a shape

[Pavertheway]

Hi,

I have percentage values in certain cells on a sheet (E3 currently displays 84% for example). I would like a shape which takes the value of E3, and then applies that percentage to a gradient fill level of the shape, starting at the bottom.

So if the shape (an Oval), was to take 50%, then the bottom half of it would be blue, and the top half empty.

Is this possible to do in VBA? I know I can use conditional formatting to match 50% to a specific colour, but I don't think conditional formatting can be used to apply to a gradient fill (as far as I know, I am very willing to be proved wrong!)

Any help would be greatly appreciated.

Thank you!

 

[Yongle]

Perhaps ...
Place in sheet module of sheet containing the 7 rectangles - runs when that sheet is activated

Private Sub Worksheet_Activate()
    Call GradShape
End Sub

Or ...
Put in sheet module for Sheet4 (may not fire if values in Sheet4 are auto-updated)

Private Sub Worksheet_Change(ByVal Target As Range)
    Call GradShape
End Sub