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

%3CLINGO-SUB%20id%3D%22lingo-sub-1994998%22%20slang%3D%22en-US%22%3EMaps%20in%20Excel%3A%20How%20can%20you%20highlight%20a%20geographic%20area%20by%20selecting%20its%20name%20from%20a%20dropdown%20menu%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994998%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20goal%20is%20to%20select%20a%20state%20name%20from%20a%20dropdown%20list%20in%20Excel%2C%20and%20have%20that%20state%20change%20color%20on%20a%20map%20of%20the%20U.S.%20also%20in%20Excel.%20I%20have%20created%20the%20map%20using%20the%20Map%20Chart%20function%20and%20set%20up%20all%20the%20reference%20tables%20as%20I%20thought%20was%20needed%2C%20but%20it%20isn't%20working.%20The%20most%20helpful%20source%20I%20have%20found%20online%20is%20this%20one%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DRfjCkHK2TXM%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DRfjCkHK2TXM%3C%2FA%3E.%20Although%2C%20after%20following%20it%20closely%2C%20it%20still%20hasn't%20worked.%20To%20note%2C%20I'm%20working%20from%20a%20Mac%2C%20and%20using%20an%20Excel%20version%2016.4.%20Unlike%20the%20video%2C%20I%20did%20not%20draw%20the%20boundaries%20around%20the%20states%2C%20but%20instead%20selected%20each%20state%20from%20a%20map%20and%20named%20them%20with%20the%20state's%20name.%20I%20tried%20the%20whole%20process%20again%20by%20inserting%20an%20editable%20map%20of%20my%20own%2C%20but%20this%20also%20didn't%20work.%3C%2FP%3E%3CP%3EThis%20is%20the%20macros%20code%20I%20used%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22hljs-keyword%22%3ESub%3C%2FSPAN%3E%20Shading()%0A%0A%0A%20%20%20%20%3CSPAN%20class%3D%22hljs-keyword%22%3EFor%3C%2FSPAN%3E%20i%20%3D%20%3CSPAN%20class%3D%22hljs-number%22%3E2%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-keyword%22%3ETo%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-number%22%3E53%3C%2FSPAN%3E%0A%20%20%20%20Range(%3CSPAN%20class%3D%22hljs-string%22%3E%22actReg%22%3C%2FSPAN%3E).Value%20%3D%20Range(%3CSPAN%20class%3D%22hljs-string%22%3E%22Data!A%22%3C%2FSPAN%3E%20%26amp%3B%20i).Value%0A%20%20%20%20ActiveSheet.Shapes(Range(%3CSPAN%20class%3D%22hljs-string%22%3E%22actReg%22%3C%2FSPAN%3E).Value).%3CSPAN%20class%3D%22hljs-keyword%22%3ESelect%3C%2FSPAN%3E%0A%20%20%20%20Selection.ShapeRange.Fill.ForeColor.RGB%20%3D%20Range(Range(%3CSPAN%20class%3D%22hljs-string%22%3E%22actRegCode%22%3C%2FSPAN%3E).Value).Interior.Color%0A%0A%20%20%20%20%3CSPAN%20class%3D%22hljs-keyword%22%3ENext%3C%2FSPAN%3E%20i%0A%0A%0A%20%20%20%20Range(%3CSPAN%20class%3D%22hljs-string%22%3E%22A6%22%3C%2FSPAN%3E).%3CSPAN%20class%3D%22hljs-keyword%22%3ESelect%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22hljs-keyword%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-keyword%22%3ESub%3C%2FSPAN%3E%0A%3C%2FPRE%3E%3CP%3EThank%20you%20for%20any%20insights%20into%20how%20to%20make%20this%20work!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1994998%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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 using the Map Chart function 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. Although, after following it closely, it still hasn't worked. 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 named them with the state's name. I tried the whole process again by inserting an editable map of my own, but this also didn't work.

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!

This video provides a step by step guide to creating a dynamic map in Excel. Part 4 will cover the actual macro programming needed for the dynamic shading. P...
0 Replies