Solved: Change a Picture or Object Color using cell values

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

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

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

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

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.

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.

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

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/Wedne...obson_ODOT.pdf

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

Thanks for your help with this...

JimS