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.
- Get an editable map of the US in which you can select different shapes that you want to highlight
- 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
- 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.
- Create an Excel drop down list with the names of all the states ($B$2 in this case)
- 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
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 If Target.Address = "$B$2" Then 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
- First, right-click on the sheet tab that has the map and select “View Code”. This will open the VB editor.
- Paste the code.
5 thoughts on “Highlight Part of a Map based on Drop Down Selection”
In the code, Is there suppose to a space between “StateNumber” or…
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
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.
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?
That’s the holy grail to me. Do you happen to know where I could find out how to do this?
Comments are closed.