Created this file based on work from Andy Pope (Showing US map and using data ranges) and Peltier Tech (also showing US map) to show a European map in colour based on the values entered. There’s a little bit of VBA, not much and of course you need to have the shapes in excel, but I though it was interesting, so I’ve posted it here to view. The idea is to be able to see by colour how good or bad each country is performing. Nice visual statistics. I’ve seen a few websites that will do this for you, but I liked the idea of doing it in Excel and since it was relatively painless, here it is. (You can of course also use this technique for other things, not just maps!)
In the attached file, you have two sheets, once with the map (MainMap) and another for the data (Control)
Each individual country on the MainMap tab is a freeform object. You can either draw these in excel yourself (tricky), or you can go to OpenClipArt.org and download the appropriate svg file, for example world map, french map etc. Then use an svg – wmf converter (or emf converter). Once the file has been converted, import the wmf or emf drawing into excel. If you use inkscpae you can open the svg directly and save as emf, nice and easy! Ungroup the wmf / emf picture on the excel sheet and excel will convert each object into a freeform shape. Give each freeform a name (the same as in the list of countries on the Control sheet. You can do this using the selection and visibility pane or by typing it in the name box next to the function bar. Obviously if you want to add more countries, you need to add and name more countries on the map as well as on the control tab.
Then go to the “Control” sheet.
A2:B9 is a named range called “STATES”, note the use of capitals! If you’ve added extra countries, use the name manager on the formulas tab of the ribbon to edit the range.
E2:E9 is also a named range “STATE_COLOURS”, but the macro assigned to the button actually looks at the formatting of the colours in the adjacent cells to this range i.e. F2:F9.
How it works is that when you change the values in B2:B9, and press the macro button, the colours on the MainMap page will change accordingly.
The vba to do this is below. I’m sure there are other ways of doing this, but vba is not my strong suit. Please note the diagonal colouring with 2 colours only works in Excel 2003, not 2010. Also this works only for exact values and not ranges (which is what I wanted), but the vba can easily be altered to look at ranges of values. Any comments or suggestions, please post below! Enjoy.
Sub ColourStates()
‘ Using the values from named range STATE
‘ And the colours from named range STATE_COLOURS
‘ re colour the map on sheet MainMap
‘
Dim intState As Integer
Dim strStateName As String
Dim intStateValue As Integer
Dim intColourLookup As Integer
Dim rngStates As Range
Dim rngColours As Range
Set rngStates = Range(ThisWorkbook.Names(“STATES”).RefersTo)
Set rngColours = Range(ThisWorkbook.Names(“STATE_COLOURS”).RefersTo)
With Worksheets(“MainMap”)
For intState = 1 To rngStates.Rows.Count
strStateName = rngStates.Cells(intState, 1).Text
intStateValue = rngStates.Cells(intState, 2).Value
If intStateValue > 9 Then
‘ stripped
With .Shapes(strStateName)
intColourLookup = Application.WorksheetFunction.Match(CInt(Left(CStr(intStateValue), 1)), Range(“STATE_COLOURS”), True)
.Fill.Patterned msoPatternWideUpwardDiagonal
.Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
intColourLookup = Application.WorksheetFunction.Match(CInt(Right(CStr(intStateValue), 1)), Range(“STATE_COLOURS”), True)
.Fill.BackColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
End With
Else
‘ single colour
intColourLookup = Application.WorksheetFunction.Match(intStateValue, Range(“STATE_COLOURS”), True)
With .Shapes(strStateName)
.Fill.Solid
.Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color
End With
End If
Next
End With
End Sub
Once again, you can find the Excel file here. Enjoy
Hi Alexandra, Thanks for this great technique which will be very useful for me. Can you share the Excel file as well?
I’ve uploaded it! Sorry for the oversight.
That’s great, thanks again!
Used this as a template for mapping out a plan for introducing cervical cancer screening in Tanzania. Thank you for your help!
You’re welcome. Glad it could be of use to you!
Hi, this technique you have shared is great. I would like to customise the vba code but I’m totally a noob to vba and would appreciate if you could help me with this: 1.) Instead of absolute values, i would like to include a range of values 2) Including rollover text in the map to indicate their values and state names. Please advise me on the vba coding to do the above two steps! Thank you so much .
Hi Jerlyn,
Send me an email to alesandra@about.me and I will be happy to help you with your project
Sent to your email ! Thanks so much ! Look forward to your reply 🙂
This is beautiful!
Thank you! Great work.
The map needs some corrections. Former Yugoslavia and Czechoslovakia should be splited on different countries. Corsica and Sardinia&Sicily should be merged with France and Italy accordingly.
I know. It’s an old map. But thank you for the comments.
Its very interesting the application, congratulation to you.
I’m trying to make a system using freeform drawing using different direction buttons. Maybe your example will help me solve this
Thank you
YOU MADE MY DAY! THANKS