Recently I was giving a half day course on heat maps and came up with the novel idea of creating a custom function which would identify the primary colour scheme for a cells interior colour. It is in an effort to save a little time in the creation of a colour scheme for heat mapping. Rather than laying down the colour and looking up the Red, Green and Blue numerical combination I simply lay the colour down and the custom function does the work for me.
Red for example has a (Red, Green, Blue) number which is Red 255, Green 0, Blue 0. This can be used to generate the colour of a shape based on criteria. You will see plenty of examples on this site when using heat maps. You set up a table which ranks achievement to a given plan then colour your map based on that achievement which is represented by a colour. You might use a traffic light set up where Red is under achievement and Green is beating target, with yellow somewhere around par. The following is the custom functions which will split out the cells interior colour into its numerical representation.
Function Red(rng) As Long
Dim c As Long
Dim r As Long
c = rng.Interior.Color
r = c Mod 256
Red = r
End Function
Function Green(rng) As Long
Dim c As Long
Dim g As Long
c = rng.Interior.Color
g = c \ 256 Mod 256
Green = g
End Function
Function Blue(rng) As Long
Dim c As Long
Dim b As Long
c = rng.Interior.Color
b = c \ 65536 Mod 256
Blue = b
End Function
If the cell you were wanting to test were in A2 you would apply the formula as follows:
B2=Red(A2)
C2=Green(A2)
D2=Blue(A2)
The colour scheme for cell A2 would be revealed and could be used to generate the back ground colour for a particular shape in Excel.
