Solved: Change a Picture or Object Color using cell values

[JimS]

I need to create a map and change the colors of the individual states based on cell values.

I was thinking of have 50 individual pictures/shapes/objects/whatever (1 for each state) and then use VBA to change the color of the individulas items.

Any ideas on how best to do something like this?

Thank...

JimS

[Kenneth Hobs]

Look for these words in a search engine for examples: choropleth map usa Excel

[JimS]

Thanks Kenneth...

Looks like what I need.

Looking at it and trying to figure out how it works.

You would have any explaination of how it works, would you?

I would need more color (other then shades of black and gray).

JimS

[JimS]

Specifically how does it convert colors to (I’m guessing) hex?
For instance; 16,711,680 would be Blue.

[Kenneth Hobs]

If I remember tomorrow, I will see if I have an example that I did a year or two back. I added some code to make it fit my needs that you might like as well.

Yes, that is the right decimal value for blue. Perhaps my colorindex chart will help.

[Kenneth Hobs]

Attached is my example map. It has some other little goodies in it like a colorpicker dialog using John Walkenbach's code. One could easily add a Selection event to set color interiors and values in the Data sheet.

Change the color values in column G on the Data sheet and run UpdateMap() in the Module mMap to update the map.

The shapes could probably be moved a bit to look better. Of course the shape colors can be changed manually easily enough.

[JimS]

Thanks - that helps a lot...

If I could only copy/paste the map (grouped) into ppt I'd have it all - well almost all.

Thanks again...

JimS

[Kenneth Hobs]

Yes, that is what I did. You can select the shapes manually and copy, or use a macro. This presentation in pdf format is about 1.5 mb in size. http://www.seaupg.org/PDF/2010/Wednesday/10Western%20Update%20Survey_KHobson_ODOT.pdf

To select all shapes on a sheet named Map:
Sheets("Map").Shapes.SelectAll

[JimS]

Thanks for your help with this...

JimS