United States Map With Dynamic Colors

KevinL · Jun 5th 2007

First of all my thanks for all the tremendous help provided by these forums. Usually I can find whatever I need on here, but I cannot find this answer...

I'm trying to make a little dynamic map of the United States where each of the states change color based on a particular cell value that corresponds to that state.

I've got the map of the United States in Excel and ungrouped so I have a collection of seperate shapes - one for each of the lower 48 States. I've named each shape (state) by it's postal abbreviation: TX, CA, NY, etc.

Now I'm trying to write a macro that will COLOR the state according to a numeric value in the cell next to the cell containing the State abbreviation. These are transit days to this state that I will calculate in another location. Like this:

TX = 3 days
CA = 1 days
OR = 1 days
NY = 7 days
KS = 3 days
FL = 7 days

I've attached a file showing how far I've gotten (not very far...) Any help would be most appreciated.

Brian Walters · Jun 5th 2007

Re: United States Map With Dynamic Colors

KevinL,

This should color the states, but not all of the states have a shape named for them i.e. AZ.

Code

Sub Macro1()
Dim rng As Range, whtColor As Integer


On Error Resume Next
    For Each rng In Range("P2:P52")
      ActiveSheet.Shapes(rng.Value).Select
      For i = 2 To 8
        If Range("R" & i).Value = Cells(rng.Row, rng.Column + 1).Value Then
          whtColor = Range("S" & i).Value
        End If
      Next i
      Selection.ShapeRange.Fill.ForeColor.SchemeColor = whtColor
    Next
      
End Sub

Display More

Also, this will put the colors next to the numbers in col T.

Code

Sub macro2()
  For i = 2 To 51
    Range("U" & i).Interior.ColorIndex = Range("T" & i).Value
  Next i


End Sub

KevinL · Jun 5th 2007

Re: United States Map With Dynamic Colors

Thank you so much I'll give it a shot.

LOL - looks like I actually missed several of the state names...

shg · Jun 5th 2007

Re: United States Map With Dynamic Colors

I just learned (http://www.ozgrid.com/forum/showthread.php?t=53791) that ColorIndex and SchemeColor are not the same; ColorIndex N corresponds to SchemeColor N+7. So you can change this

Code

Selection.ShapeRange.Fill.ForeColor.SchemeColor = whtColor

to

Code

Selection.ShapeRange.Fill.ForeColor.SchemeColor = whtColor + 7

Biz · Jun 5th 2007

Re: United States Map With Dynamic Colors

I like this exciting stuff, but when I run macro it does not colour all states.

KevinL · Jun 5th 2007

Re: United States Map With Dynamic Colors

That's because I didn't get all the states named correctly on my sample file. Found out you actually have to type in the box and hit <Enter> not just click another shape or <tab> off of the name field.

After I named all the shapes correctly, it works like a charm.

billyj · Jun 5th 2007

Re: United States Map With Dynamic Colors

Could you post and example of the final results of the project.

BillyJ

Biz · Jun 6th 2007

Re: United States Map With Dynamic Colors

Quote from KevinL

That's because I didn't get all the states named correctly on my sample file. Found out you actually have to type in the box and hit <Enter> not just click another shape or <tab> off of the name field.

After I named all the shapes correctly, it works like a charm.

Hi KevinL,
Could you please post updated version so we can learn how make funky stuff you?

Biz

JimFuller1 · Jun 7th 2007

Re: United States Map With Dynamic Colors

KevinL,

Does your map carry any copyright restrictions? If it doesn't, I think you should post it over on the "That's Cool" board because it could have many uses besides the one your post covers.

Jim

United States Map With Dynamic Colors

Participate now!

Share