color grid

[Anomandaris]

hi guys,
need help again, this one appears a tad more complicated


What I'm trying to do is create something like a heatmap but not using the surface charts in excel, rather with text boxes. I dont know if this is the best way....It will be an 8 by 8 grid, with each box in the grid having 3 vertical cells- showing different statistical values calculated from another sheet.
I need the Current, average and z-score values to show up in each grid box in that order. then the box should be colored according to the value - negative values in different shades of blue, lowest being darkest (but not too dark as i'd like to read the number), positive values are red with largest values being darker shades of red lower values close to 0 having a whitish shade of red.
The coloring should depend on z-score value....actually i'll have option buttons setup on the page so the coloring can be determined by which option i choose (current, average or zscore), but for now lets just get it for the zscore.
here's the sheet, the data is on DV-IV Spread in the upper table, that has to be displayed in the heatmap sheet.....i have outlined what the grid would look like.
thanks

[Anomandaris]

I'm trying to use
RGB(255,255,255) codes for color shading......but I can never get it to be red or blue, its giving me weird brown and greyish colors, it does do green as well but i dont need green.

any thoughts? could it have something to do with me using the XP version of excel?

thanks

[mdmackillop]

I believe you are limited to the number of colours in the palette
Give this a try

Sub Colours()
For i = 5 To 255 Step 10
For j = 5 To 255 Step 10
For k = 5 To 255 Step 10
a = a + 1
Cells(a, 1) = i
Cells(a, 2) = j
Cells(a, 3) = k
Cells(a, 4).Interior.Color = RGB(i, j, k)
Cells(a, 5) = Cells(a, 4).Interior.ColorIndex
Next
Next
Next
End Sub

[Anomandaris]

thanks man,

it was a problem with my workbook, i got an addin or smthn that limited teh color range, i opened another workbook, there i get all teh colors

now i just need to figure out the code to get the grid to work