Animated Heat Map in Excel

The following article is an example of how to make a meat map animate.  I have created some animated charts and this is an addition to this technique.  There are numerous heat maps on the site this one combines a map of the counties of England and the boroughs of London to produce heat maps on Housing Affordability, Average House Price and Income in the respective maps.  The period displayed is from 1997 to 2013. 

Animated chart

The following procedure will change a cell value and wait 1 second before moving to the next year.  It actually goes from 97' to 2013.  The Excel VBA coding which drives the file is as follows;

Sub Animate() 'Excel VBA animate procedure. Dim i As Integer
For i=1997 To 2013
[b1]="Yr " & i
Application.Wait (Now + TimeValue("00:00:01"))
Main
Next i
End Sub

The procedure called main is as follows. This procedure is mapped out more fully in the Heat Maps in Excel article.

Option Explicit

Sub Main() 'Excel VBA Drop down, Main process
Dim shp As Shape
Dim ar As Variant
Dim var As Variant
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim lup As Variant
Dim fn As Range

Set fn=Sheet3.Range("N2", Sheet3.Range("N" & Rows.Count).End(xlUp)).Find([b2])
Set fn=fn.Offset(, 1).Resize(5, 1)
fn.Copy [B9]

lup=[{"Income Grade 97","Multiple 97","AvgPrice 97"}]
n=Sheet2.Range("A1:CZ1").Find(lup(Sheet1.[C2])).Column + [C1]
var=Sheet3.Range("U2", Sheet3.Range("W65536").End(xlUp)) 'List Sheet
ar=Sheet2.Range("B2", Sheet2.Range("B65536").End(xlUp)) 'London Sheet

For i=1 To UBound(ar) 'Excel vba start loop
j=Application.VLookup(ar(i, 1), Sheet2.Range("B:DG"), n, 0) 'London Sheet
Set shp=Sheet1.Shapes(ar(i, 1))
shp.Fill.ForeColor.RGB=RGB(var(j, 1), var(j, 2), var(j, 3))
Next i
End Sub

The code works by finding what variable is selected in the combo box and applying a colour scheme based on the colour codes in the List sheet.  The Excel VBA and animated heat map is in the file below.  The colours can be adapted to suit your needs.