Sean Lauber:Heat maps in Excel

To enable macros in Excel:

1. Click the microsoft office button (top left), go to Excel options

2. In the "Popular" tab, check the show developer ribbon

3. Go to the Developer ribbon, click Macro Security and check Enable all macros

4. In the Developer ribbon, click Visual Basic

5. Click Insert/Module to add a module and input your macro

Use the following macro (developed by Paul Hengen):

 Sub heatmap()
 Dim myrange As Range
 Dim colchoice As Integer
 Set myrange = Application.InputBox("Select a range of cells", rangetocheck, , , , , , 8)
 For Each cell In myrange
 Select Case cell.Value
 Case Is >= 8
    colchoice = 1
    fontchoice = 2
 Case Is >= 7
    colchoice = 3
 Case Is >= 6
    colchoice = 45
 Case Is >= 5
    colchoice = 6
 Case Is >= 4
    colchoice = 19
 Case Is >= 3
    colchoice = 20
 Case Is >= 2
    colchoice = 8
 Case Is >= 1
    colchoice = 41
 Case Is < 1
    colchoice = 5
    fontchoice = 2
 End Select
 cell.Interior.ColorIndex = colchoice
 cell.Font.ColorIndex = fontchoice
 fontchoice = 1
 Next
 End Sub

Determine an appropriate range by dividing your highest value by 8 (in the macro the default is 8/8=1), and change the appropriate values "case < x" "case is >=x", etc. so that these values are indicated by different colors.

This macro was developed by Paul Hengen with BiteSizeBio (http://bitesizebio.com/articles/how-to-create-a-heatmap-in-excel/)