-
color grid
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
-
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
-
I believe you are limited to the number of colours in the palette
Give this a try
[vba]
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
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules