display excel shape with different colour and position dependent on a value.

[Andrew]

Hi


I am trying to create an excel template similar to this image: http://www.direct.gov.uk/prod_consum_dg/groups/dg_digitalassets/@dg/documents/digitalasset/dg_170265.gif


where the pointers on the right side would change colour and position based on a value (which would also be displayed in the shape).


I know how to do the latter I think, just linking a text box to a cell, but how to change colour and position? Can this be done without VBA?


I have seen a vba method to change colour of a shape at http://www.excelcharts.com/blog/how-to-create-thematic-map-excel/


but would like to avoid vba if possible, and how can the shape move to a different position based on the value?


Any help welcome!

 

[Hui]

Andrew


It can't be done without VBA.


Don't be put off by VBA.


The code to do what you want is mostly found in one of my ExcelHero posts at: http://www.excelhero.com/blog/2010/09/excel-optical-illusions-week-30.html

 

[Andrew]

Thanks Hui,


Boy, that looks a bit scary. Unfortunately I am a complete novice at vba.


As an alternative, maybe the code to show how to change the color based on a value at http://www.excelcharts.com/blog/how-to-create-thematic-map-excel/ can be altered so instead of moving the indicator it shows white (on a white background) when the value is not in range - ie so there are several pointers (one for each grade), and only the one which corresponds to the value is colored, all others are white?


Unfortunately I'm not certain how to do that either.


Any ideas on how to do that?


thanks!