Sean Lauber:Heat maps in Excel

From OpenWetWare

(Difference between revisions)
Jump to: navigation, search
Current revision (09:46, 26 October 2012) (view source)
 
Line 2: Line 2:
1. Click the microsoft office button (top left), go to Excel options
1. Click the microsoft office button (top left), go to Excel options
 +
2. In the "Popular" tab, check the show developer ribbon
2. In the "Popular" tab, check the show developer ribbon
 +
3. Go to the Developer ribbon, click Macro Security and check Enable all macros
3. Go to the Developer ribbon, click Macro Security and check Enable all macros
 +
4. In the Developer ribbon, click Visual Basic
4. In the Developer ribbon, click Visual Basic
 +
5. Click Insert/Module to add a module and input your macro
5. Click Insert/Module to add a module and input your macro
-
Use the following macro:
+
Use the following macro (developed by Paul Hengen):
   Sub heatmap()
   Sub heatmap()
Line 44: Line 48:
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.
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/)

Current revision

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/)

Personal tools