# Highlight Part of a Map based on Drop Down Selection

Here is a neat trick for people who use maps in Excel. In this blog, I will explain how to highlight map in excel based on a drop down selection.

I have taken an editable map of the US. The idea is to highlight a state in the US when its name is selected from a drop down.

Something as shown in the pic below: Now before I show you the code, here are a few prerequisites for this trick.

1. Get an editable map of the US in which you can select different shapes that you want to highlight
2. Create a list of all the 50 states in one column, and on the column to its right I wrote State 1, State 2, and so on 3. Name each shape on the map. For example, I have 50 shapes here for 50 states, and I have named each Shape as State 1, State 2, State 3, and so on..To do this, select any shape, and go to the Name Box, which is on the left of formula bar, and enter its name from the State Number column. For example, I selected Alabama and named it State 1. 4. Create an Excel drop down list with the names of all the states (\$B\$2 in this case) 5. Use the Vlookup function to extract the state number when a state is selected from the drop down. Here is the formula that I have used in cell \$B\$3
=VLOOKUP(B2,’State List’!\$B\$3:\$C\$52,2,FALSE)
##### Code to Highlight Map in Excel
```Private Sub Worksheet_Change(ByVal Target As Range)
Dim N As Integer
Dim ShapeName As String
N = ActiveSheet.Shapes.count
For i = 1 To N
ShapeName = ActiveSheet.Shapes(i).Name
If Left(ShapeName, 6) = "State " Then
ActiveSheet.Shapes(i).Select
With Selection.ShapeRange.Fill
.Visible = msoFalse
.Transparency = 1
End With
End If
Next i
StateNumber = Range("\$B\$3").Value
ActiveSheet.Shapes(StateNumber).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
.Solid
End With
ActiveSheet.Range("\$B\$2").Select
End If
End Sub```

Follow these simple steps while pasting this code

1. First, right-click on the sheet tab that has the map and select “View Code”. This will open the VB editor. 2. Paste the code.  FREE EXCEL BOOK

## Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

### 5 thoughts on “Highlight Part of a Map based on Drop Down Selection”

1. Hi
In the code, Is there suppose to a space between “StateNumber” or…

2. Hi…this is cool…but once highlighted, is it possible to show some data point…also I was looking at a way to plot data points statewise on a map..pls guide

3. Hello sumit! Would you please tell something about Excel VBA? I mean what language VBA communicates in and how can one learn this language to mould the excel work according to one’s desires.

4. How would you go about modifying the code to allow multiple states to be selected and highlighted on the map? For example if cells B2, B3, and B4 allowed user input and had Alabama, Alaska, and Arkansas with shape lookups in cells C2, C3, and C4, respectively, how would the code be expanded to include multiple input cells and highlight the multiple corresponding shapes?

• Hi
That’s the holy grail to me. Do you happen to know where I could find out how to do this?