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 onehttps://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:
For i = 2To53
Range("actReg").Value = Range("Data!A" & i).Value
Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range("actRegCode").Value).Interior.Color
Thank you for any insights into how to make this work!