VBA autoshape arrows to show performance

Morning all. I'm trying to use VBA to generate autoshape arrows to track changes in values.

Something allong the lines of Column B (Jans data) C (Febs Data) E (autoshape arrow, green up, for improvement, or red downwards for a worsening).

The problem I'm having is postioning my autoshapes. The only way I can do it at present is to keep all columns and rows a standard width, and position using multiples of those.

Is there anyway to set the autoshape to the cell height and width, and position it within the boundries of the cell? This way I don't have to worry about rewiting my code every time I change a cell width?

Which version of Excel are you using?

The cell has properties you can use to size you shape.

Please Login or Register  to view this content.

I'm on excel 2003.

I'm using those values to position the autoshapes at the moment, but if I change the width of a cell I have to manuallly move the "Left" value, and likewise with the height values for row heigh changes.

I was thinking I could do a sum of the heights of all the previous cells (and the same for the width), but it seems a clumbersome way of doing it, and hoped there might be a simple way to get the top left position of a cell?

post an example workbook with your code.

include an example of how the arrow should look

Please Login or Register  to view this content.

I've done a simplified snippet, (refers attached example). The problem is I want the left and top positions determined by the position of F13 and F14 etc not just manually typed.

Here is the code

Please Login or Register  to view this content.

See attached, which also includes a formula approach and custom number format

Many thanks Andy.