Create a thermal map of Europe

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!)europe map

In the attached file, you have two sheets, once with the map (MainMap) and another for the data (Control)

colour ranges

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 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.

excel named range selection and visibility

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


‘ single colour

intColourLookup = Application.WorksheetFunction.Match(intStateValue, Range(“STATE_COLOURS”), True)

With .Shapes(strStateName)


.Fill.ForeColor.RGB = rngColours.Cells(intColourLookup, 1).Offset(0, 1).Interior.Color

End With

End If


End With

End Sub

Once again, you can find the Excel file here.  Enjoy

Alesandra Blakeston

13 thoughts on “Create a thermal map of Europe

  1. Hi Alexandra, Thanks for this great technique which will be very useful for me. Can you share the Excel file as well?

  2. Used this as a template for mapping out a plan for introducing cervical cancer screening in Tanzania. Thank you for your help!

  3. 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 .

  4. 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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s