Sean Lauber:Heat maps in Excel

From OpenWetWare

(Difference between revisions)
Jump to: navigation, search
(New page: Use the following macro: Sub heatmap() Dim myrange As Range Dim colchoice As Integer Set myrange = Application.InputBox("Select a range of cells", rangetocheck, , , , , , 8) For...)
Line 1: Line 1:
 +
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:
Use the following macro:
Line 24: Line 32:
   Case Is >= 1
   Case Is >= 1
     colchoice = 41
     colchoice = 41
-
   Case Is < 0
+
   Case Is < 1
     colchoice = 5
     colchoice = 5
     fontchoice = 2
     fontchoice = 2
Line 33: Line 41:
   Next
   Next
   End Sub
   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.

Revision as of 09:44, 26 October 2012

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:

 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.

Personal tools