Sean Lauber:Heat maps in Excel
From OpenWetWare
Jump to navigationJump to search
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/)