US Thermal Map

msofficeshare logo

Thermal Map is also called Choropleth Map or Heat Map.  The US Thermal Map is one of the most complicated programs to write.  It involves creating a map by slicing out each individual state and then put the states back together to form the US map.  Each state will be named with a code (state abbreviation code).  Then a Visual Basic model is written to control the color of each state based on the value associated with it.  Usually the darker the color, the higher the value associated with such state.

The thermal map is significantly more complicated to build than the other maps that I created in this website (e.g. US Map with Smiley Faces, World Map with Bar Charts, and World Map with Population Indicators).  However, I consider that the thermal map is inferior to other maps with indicators.  The thermal map has the following weaknesses besides the difficulties to build:

  1. The color may look similar for the ranges close to each other, especially if you want to have more categories.  For example, a map with 5 color differentials may have one color looked quite different from another.  A map with 10 color differentials may have one color looked somehow similar to its neighboring range of colors.
  2. You can consider to using totally different colors for each category, for example, use blue for lower range of values, purple with middle range, and red with higher range.  Then the readers would have to frequently refer to the legend to make sure they read the map correctly.  In fact, using a range of similar colors is easier for readers to read, for example, very light red for lower range of values and very dark red for higher range of values.
  3. The choice of color is difficult to model with the use of color identification system in Microsoft.  Microsoft assigns each color a specific code which ranges from 7 to 8 digits.  Unless you have such a list of color codes, it will become a process of trial and error to get the colors you desire.
  4. The Visual Basic coding is more complicated compared with the other maps using indicators because of the color coding.
  5. It is difficult to read if the state is too small.  For example, the original 13 states of the US are generally small and the readers have to look carefully to distinguish them.  Washington DC is another problem because it is just a “dot” on the map.  It has to be singled out to express it some other way.

Please follow the below steps to create a US Thermal Map:

Requirement:  Visual Basic Knowledge

File to download:  US Thermal Map.xlsm

Related articles:  World Map with Population IndicatorsWorld Map with Bar ChartsUS Map with Smiley Faces

Step 1:  Create the data.  Please note that I need the column C (State Abbreviation) because I named the states by the state abbreviations.  If you name the states by the full name of the State, you don’t need column C.

pic1 us thermal map
pic2 us thermal map

Step 2:  Create a US map.  This step is quite complicated and I don’t want to go into the details because it involves using some advanced graphic software to slice out the state and make each state to become an individual entity so that you can change the properties of such individual state.  For example, you see that Texas (TX) State is actually an individual entity (object) with its own properties that are totally separated from other states.  The object of Texas State is named as “TX” using the state abbreviation codes.

You can use the map that I included in the “File to download:” (US Thermal Map.xlsm) so you don’t have to spend a lot of time to create one yourself.

pic3 us thermal map

Step 3:  Choose your color scale and find out their color codes.  I chose 10 color differentials for my example.

pic4 us thermal map

Step 4:  Write the Visual Basic codes.

I used the dynamic referencing for the color range. The color of the state depends on the value of such state relative to the state with the highest value.  In my example, Nebraska (NE) has the highest value ($4,935) among all states.  So the color range will be classified into 10 categories from $0 to $4,935.

The formula to be used is StatePercent = StateValue / MaxDataValue * 100 where “* 100” is to change the number from percentage to general value (e.g. from 90% to 90).

Since there are 50 states in the US (ignoring Washington DC, Puerto Rico, and Virgin Island), the VB code will be looped for 50 times.  The looping will call on a subroutine called “ChooseColor” to find the right color category associates with the values of such state and then returns the color category of the state back to the main module (“MapColor”).  The color property of the state object will be changed by the Visual Basic module when you run it.

Please note that I created an additional subroutine program called “LegendColor”.  It plots the color to the legend in the “Map” worksheet (cell S14 to S23).  This subroutine is not a mandatory program to run this report.  It does help me to plot the legend if I want to change the colors of the color categories.  When you need to run the VB module, just click the “Color Mapping” on the “Map” worksheet.  When you click the “Color Mapping” button, it will not initiate the “LegendColor” module.

Below are the codes of the VB modules.  They include the main module (“MapColor”) and the subroutine (“ChooseColor”), but exclude the “LegendColor”. 

pic5 us thermal map

Sub MapColor()

    Dim StateRange As Range

    Dim MaxDataValue As Double, StateValue As Double, StatePercent As Double

    Dim StateCount As Integer

    Dim StateAbbr As String


    Application.ScreenUpdating = False


    Set StateRange = Range("MapData")

    MaxDataValue = Application.WorksheetFunction.Max(StateRange)


    If MaxDataValue = 0 Then

        MaxDataValue = 0.01         'to avoid division by zero error

    End If




    StateAbbr = Range("FirstData").Value

    StateValue = ActiveCell.Offset(0, 1).Value

    StateCount = 1


    For StateCount = 1 To 50

        StatePercent = StateValue / MaxDataValue * 100

        Call ChooseColor(StatePercent, StateAbbr)



        ActiveCell.Offset(1, 0).Select

        StateAbbr = ActiveCell.Value

        StateValue = ActiveCell.Offset(0, 1).Value

    Next StateCount




    Application.ScreenUpdating = True

End Sub


Sub ChooseColor(StatePercent, StateAbbr)

    Dim StateColor As Long


    Select Case StatePercent

        Case 0 To 10

            StateColor = 16777215

        Case 10 To 20

            StateColor = 13097981

        Case 20 To 30

            StateColor = 8562164

        Case 30 To 40

            StateColor = 8225247

        Case 40 To 50

            StateColor = 5071062

        Case 50 To 60

            StateColor = 5193429

        Case 60 To 70

            StateColor = 3947716

        Case 70 To 80

            StateColor = 2824370

        Case 80 To 90

            StateColor = 2428045

        Case 90 To 100

            StateColor = 2031719

    End Select


    'Debug.Print "StateColor", StateColor



    With ActiveSheet.Shapes(StateAbbr)

        .Fill.ForeColor.RGB = StateColor

    End With

End Sub

This is the result of color mapping.