Maps in Excel: How can you highlight a geographic area by selecting its name from a dropdown menu?

user_14833899 0
Viewed 211

The goal is to select a state name from a dropdown list in Excel, and have that state change color on a map of the U.S. also in Excel. I have created the map, and set up all the reference tables as I thought was needed, but it isn't working. The most helpful source I have found online is this one https://www.youtube.com/watch?v=RfjCkHK2TXM. To note, I'm working from a Mac, and using an Excel version 16.4. Unlike the video, I did not draw the boundaries around the states, but instead selected each state from a map and labeled them with the state's name.

This is the macros code I used:

Sub Shading()


    For i = 2 To 53
    Range("actReg").Value = Range("Data!A" & i).Value
    ActiveSheet.Shapes(Range("actReg").Value).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range("actRegCode").Value).Interior.Color

    Next i


    Range("A6").Select
End Sub

Thank you for any insights into how to make this work!

maps vba excel
1 Answers

As I said in my comments, I’ll provide an answer that works with Office365/Windows 10 with the hope that there’s enough parallels for you to adapt it to your needs. For this to be any use to you, the following 2 basic assumptions must be true:

  1. That Excel for Mac 2D Map Charts are what you’ve used to create your map, and that the Chart is informed by a data series, and
  2. Your sole purpose is to highlight on the map the state you’ve picked via your dropdown list

If either of these things are false, read no further.

Step 1 – Set up the Data Series

I don’t know anything about your layout, so I’ll describe my layout here – simply change it to match what you’ve got.

Headers in cells A1 B1 and C1 List of states from A2 down to A51 say (in my case, also the ‘source’ for the dropdown cell) List of values from B2 down to B51 say. Dropdown cell in C2

In cell B2, overwrite the data with the following formula: =IF(A2=$C$2, 0, 1) NOTE the absolute reference $C$2. Copy this formula down column B – overwriting all existing data.

What will this do? Whichever state you select via the dropdown will have a 0 in its data series – all other states will have a 1.

Step 2 – Format the Data Series

Right click on any state on the map and select Format Data Series (or however you do that on Excel for Mac 2D Map Charts) Select Series Color In Minimum – Lowest Value change color to red (or your choice) In Maximum – Highest Value change color to blue (or your choice)

Expected Result

Now, whenever you change the state via the dropdown will put a 0 in that state’s data series – which is the minimum value in the series, and a 1 in all other states – which is the equal maximum value in the series. The state selected will be red – all others will be blue.

Let me know how it goes.

Admin1
Related