Q: assign field background color dynamically according to field values

[Stephan Schnitzler]

Hi,

I need to assign background colors in an excel sheet
according to the integer values within the fields.

i.e. I have Matrix with entries between 1 and 100, where
1 should be a dark color, 100 a bright color and the values
between accordingly.

I have seen this browsing matrices in Maple.

Conditional Formatting doesn't do the job as
I need more subdivisions than I am willing to do
by hand.

Does anybody know a resource that could help me
or is anybody able to provide me with a VBA code?

Thanks a lot - Stephan

 

[Bob Phillips]

http://xldynamic.com/source/xld.CFPlus.Download.html

 

[Stephan Schnitzler]

http://xldynamic.com/source/xld.CFPlus.Download.html

thanks, that's a nice tool, but doesn't do my job.
With this it's still necessary to do the conditions
by hand. As the value-range always changes, it
would be to much work.

I need a script or something that reads all the
fields, sets the lowest number to dark, the
highest to bright (or something other colors),
and all the numbers in between to other colors.

Regards, Stephan.

 

[Tim Williams]

You can do this but you should be aware of a couple of points around cell
colors.

1. If you assign a background color to a cell via code then Excel will map
it to the nearest color in the workbook's color table
2. ....so you'll be limited to a maximum number of different colors.

Neither of these is a big issue since you can remap the workbook's color
table to match the colors you need to use, and using more than 20 or so
colors is normally enough.

I've done both simple gradient and "heatmap" type coloring (blue=low,
red=high) in Excel and it works well.

Contact me via email if you need sample code or I can post some here if you
prefer.

Tim.

 

[Stephan Schnitzler]

That's exactly what I need.
The limitations don't matter at all.
It's just for getting an fast overview
of the number distributions.
Thank you very much, I would appreciate
if you could send me some code.
Regards, Stephan

ps: The heatmap would be perfect!
That's how I've seen it in Maple and
it suits fine.

 

[Tim Williams]

Here is a function (found I forget where, so I cannot take any credit
for it) to calculate the "heatmap" colors given a value and a min/max
scale.

'Return a RGB colour value c given a scalar v in the range [vmin,vmax]
' The colour is clipped at the end of the scales if v is outside
' the range [vmin,vmax]

Function GetColor(v, vmin, vmax) As Long

Dim dv As Single, rV, gV, bV

rV = 255
gV = 255
bV = 255

If v < vmin Then v = vmin
If v > vmax Then v = vmax
dv = vmax - vmin

If v < (vmin + 0.25 * dv) Then
rV = 0
gV = 255 * (4 * (v - vmin) / dv)
ElseIf v < (vmin + 0.5 * dv) Then
rV = 0
bV = 255 * (1 + 4 * (vmin + 0.25 * dv - v) / dv)
ElseIf v < (vmin + 0.75 * dv) Then
rV = 255 * (4 * (v - vmin - 0.5 * dv) / dv)
bV = 0
Else
gV = 255 * (1 + 4 * (vmin + 0.75 * dv - v) / dv)
bV = 0
End If

GetColor = RGB(rV, gV, bV)

End Function


To set your workbook color palette

'sets the workbook color values in new file to allow the scale to
display exactly
' colors get remapped otherwise. This is using a largish number of
colors, but 20 is probably enough
' depending on how subtle your variations are.
For n = 1 To 52
ActiveWorkbook.Colors(n) = GetColor(n - 1, 0, 51)
Next n
tempo.Colors(53) = RGB(255, 255, 255) 'white

Then just figure out what color index to set your data cells to based
on the min/max and value for each cell.

'c=cell being colored
'temp=c.value
'valMin,valMax=min & max values from range being
mapped.
If temp < valMin Then
cIndex = 1
ElseIf temp > valMax Then
cIndex = 51
Else
fracCon = (temp - valMin) / (valMax - valMin)
cIndex = 1 + 50 * (fracCon)
End If
c.Interior.ColorIndex = cIndex

Cheers,
Tim.