color code states based on different values in the dropdown list

[blyzzard]

I have a US map containing different shapes for states with state names
I have to color code us states according to the values of variables Rate1, rate2,Expense etc
If rate1<=3 then color=red if 3 <rate1<=6 then color=blue if 6<rate1 then color=green
Same has to be done with other variables like Rate2, Expenses etc.
The data in the sheet 1is in format
States Rate1
Alabama 3
Alaska 7 and so on
The data in sheet2 is
States Rate2
Alabama 4
Alaska 2
Same is the sheet3 for expenses
Now I have defined name as STATE1 containing range for States and Rate1
Also COLOR containing range for colors
STATE2 containing range for states and Rate2 and same for expenses.
i.e In the spreadsheet I have defined
Color Value
1 Red
2 Blue
3 Green
Now In the sheet named Map(containing US map), I created a dropdown validation list containing Rate1, Rate2, Expenses etc and I want to color code map based on the value selected in the list.
The code is below:
It is only working for Rate1 but the same logic does not work for Rate2.
Also for Rate1, the color for only first State “Alabama” is incorrect.

Sub Colorcode()

Dim intstate as Integer
Dim strStateName as String
Dim intStateValue as Integer
Dim intcolorlookup as integer
Dim rngState1 as Range
Dim rngState2 as Range
Dim rngColors as Range

Set rngState1=Range(ThisWorkbook.Names(“STATE1”).RefersTo)
Set rngColors1=Range(ThisWorkbook.Names(“COLOR”).RefersTo)
Set rngState2=Range(ThisWorkbook.Names(“STATE2”).RefersTo)

With Workshtees(“Map”)
If selection.value=”Rate1” then
For intstate=1 to rngStates1.rows.count
strStateName=rngState1.Cells(intstate,1).Text
intStateValue=rngState1.cells(intstate,1).Value
if intStateValue<=3 then
intcolorlookup=1
with .Shapes(strStateName)
.FillSolid
.Fill.ForeColor.Rgb=rngColors1.Cells(intcolorlookup,1).offset(0,1).Interior.Color
End with
Elseif intStateValue<=6 and intStateValue>3 then
Intcolorlookup=2
with .Shapes(strStateName)
.FillSolid
.Fill.ForeColor.Rgb=rngColors1.Cells(intcolorlookup,1).offset(0,1).Interior.Color
End with

Else
If intStateValue>6 then
Intcolorlookup=3
with .Shapes(strStateName)
.FillSolid
.Fill.ForeColor.Rgb=rngColors1.Cells(intcolorlookup,1).offset(0,1).Interior.Color
End with
End if
End if
Next
Else if Selection.Value=(“Rate2”) then
For intstate=1 to rngStates2.rows.count strStateName=rngState2.Cells(intstate,1).Text
intStateValue=rngState2.cells(intstate,1).Value
if intStateValue<=3 then
intcolorlookup=1
with .Shapes(strStateName)
.FillSolid
.Fill.ForeColor.Rgb=rngColors1.Cells(intcolorlookup,1).offset(0,1).Interior.Color
End with
Elseif intStateValue<=6 and intStateValue>3 then
Intcolorlookup=2
with .Shapes(strStateName)
.FillSolid
.Fill.ForeColor.Rgb=rngColors1.Cells(intcolorlookup,1).offset(0,1).Interior.Color
End with

Else
If intStateValue>6 then
Intcolorlookup=3
with .Shapes(strStateName)
.FillSolid
.Fill.ForeColor.Rgb=rngColors1.Cells(intcolorlookup,1).offset(0,1).Interior.Color
End with
End if
End if
Next
End if
End with
End sub()

Any suggestion would be appreciated.

Thanks
Blyzzard

[johnjohns]

Please follow the rules and use code tags

rgds

johnjohns

[blyzzard]

I have a US map containing different shapes for states with state names
I have to color code us states according to the values of variables Rate1, rate2,Expense etc
If rate1<=3 then color=red if 3 <rate1<=6 then color=blue if 6<rate1 then color=green
Same has to be done with other variables like Rate2, Expenses etc.
The data in the sheet 1is in format
States Rate1
Alabama 3
Alaska 7 and so on
The data in sheet2 is
States Rate2
Alabama 4
Alaska 2
Same is the sheet3 for expenses
Now I have defined name as STATE1 containing range for States and Rate1
Also COLOR containing range for colors
STATE2 containing range for states and Rate2 and same for expenses.
i.e In the spreadsheet I have defined
Color Value
1 Red
2 Blue
3 Green
Now In the sheet named Map(containing US map), I created a dropdown validation list containing Rate1, Rate2, Expenses etc and I want to color code map based on the value selected in the list.
The code is below:
It is only working for Rate1 but the same logic does not work for Rate2.
Also for Rate1, the color for only first State “Alabama” is incorrect.

Please Login or Register  to view this content.

Any suggestion would be appreciated.
Thanks