Sean Lauber:Heat maps in Excel: Difference between revisions
From OpenWetWare
Jump to navigationJump to 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...) |
No edit summary |
||
| 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 < | 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 07: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.
