I'm using a US Colour Map tool Andy Pope created (I sent him a PM as well). It's a cool gadget for coloring individual states based on criteria entered into a table, e.g., enter 1 next to the state name Alaska, run macro, Alaska colored red.
I had to add Puerto Rico to the map. I've included PR in the States Named Range and have hyperlinked the shape to its respective worksheet in the file. When I try to test the coloring, I receive the following error: With .Shapes(strStateName)
I usually can adapt the VBA others have created to meet my needs. However, I'm not familiar with looking at some of the code and knowing what it's telling me and how to fix it.
Here are my steps:
-I've hand drawn the shape of PR onto the MainMap worksheet.
-I've hyperlinked the shape (like the other states) to a newly created worksheet titled "Puerto Rico."
- I've added an AutoTip so that when the user rolls over PR, the words Puerto Rico appear.
- I've inserted Puerto Rico below Pennsylvania on the Control worksheet.
- I've updated the named range States to include all of the states.
Here's the code being used to "recolor" the map:
Can anyone help me figure out how to correct this? Thank you so much!!
Have a great day!
I had to add Puerto Rico to the map. I've included PR in the States Named Range and have hyperlinked the shape to its respective worksheet in the file. When I try to test the coloring, I receive the following error: With .Shapes(strStateName)
I usually can adapt the VBA others have created to meet my needs. However, I'm not familiar with looking at some of the code and knowing what it's telling me and how to fix it.
Here are my steps:
-I've hand drawn the shape of PR onto the MainMap worksheet.
-I've hyperlinked the shape (like the other states) to a newly created worksheet titled "Puerto Rico."
- I've added an AutoTip so that when the user rolls over PR, the words Puerto Rico appear.
- I've inserted Puerto Rico below Pennsylvania on the Control worksheet.
- I've updated the named range States to include all of the states.
Here's the code being used to "recolor" the map:
Code:
'
' Written by Andy Pope © 2003
'
Option Explicit
Sub ColourStates()
'
' Using the values from named range STATE
' And the colours from named range STATE_COLOURS
' re colour the map on sheet MainMap
'
Dim intState As Integer
Dim strStateName As String
Dim intStateValue As Integer
Dim intColourLookup As Integer
Dim rngStates As Range
Dim rngColours As Range
Set rngStates = Range(ThisWorkbook.Names("STATES").RefersTo)
Set rngColours = Range(ThisWorkbook.Names("STATE_COLOURS").RefersTo)
With Worksheets("MainMap")
For intState = 1 To rngStates.Rows.Count
strStateName = rngStates.Cells(intState, 1).Text
intStateValue = rngStates.Cells(intState, 2).Value
If intStateValue > 9 Then
' stripped
With .Shapes(strStateName)
intColourLookup = Application.WorksheetFunction.Match(CInt(Left(CStr(intStateValue), 1)), Range("STATE_COLOURS"), True)
.Fill.Patterned msoPatternWideUpwardDiagonal
.Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
intColourLookup = Application.WorksheetFunction.Match(CInt(Right(CStr(intStateValue), 1)), Range("STATE_COLOURS"), True)
.Fill.BackColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
End With
Else
' single colour
intColourLookup = Application.WorksheetFunction.Match(intStateValue, Range("STATE_COLOURS"), True)
With .Shapes(strStateName)
.Fill.Solid
.Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
End With
End If
Next
End With
End Sub
Can anyone help me figure out how to correct this? Thank you so much!!
Have a great day!