Correlation Map with color codes

[Rasm]

All
I have attached a file with an extract of my code

[Admin]

Hi Rasm,

See this couple of links:

1. AJP Excel Information - Progress meters (http://www.andypope.info/vba/pmeter.htm)

2. Excel (http://www.xcelfiles.com/ProgressBar.html)

[Rasm]

Admin
Thanks for the two links - I very much can use that - But my question was how to change the fill color in the sheet containg all the R values (the one being created) - My sheet name says RSQ but in reality it is the worksheetfunction Correlation and not RSQ that I used - sorry - so I am trying to add fill colors to each cell according to what the value is in that cell - going from a scale of 1 to -1.

So for instance the fill color for 0.98 is one shade of green where as a value of 0.92 is a slightly ligther shade of green - 0.77 is an even lighter shade on green and so on. This way I have a graphical map that easily tells me where I have high correllation or if you plot the absorbencies vs absorbencies where I have covariance. When it crosses over from positive to negative a new color should be gradually changing - so going from a light yellow to a dark yellow as we approach an R of -1.

FYI -- The absorbencies are my X values and the Wet chemistry are my Y values in the matrix.

Thanks
Rasm

[Admin]

Hi,

ok. so why don't a conditional formatting ??

I just recorded a macro. See if this is what you are after.


With Range("$G$5:$BZ$48")
.FormatConditions.Delete
.FormatConditions.AddColorScale ColorScaleType:=3
.FormatConditions(Selection.FormatConditions.Count ).SetFirstPriority
.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
With .FormatConditions(1).ColorScaleCriteria(1).FormatC olor
.Color = 7039480
.TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With .FormatConditions(1).ColorScaleCriteria(2).FormatC olor
.Color = 8711167
.TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
With .FormatConditions(1).ColorScaleCriteria(3).FormatC olor
.Color = 8109667
.TintAndShade = 0
End With
End With

[Rasm]

Admin
I have not tried your answer yet - But I will next.

But in the meantime - I updated my code to better illustrate what I am trying to do - Now it adds colors on the sheet it creates (you have to run code - the sheet is over 100KB so too big if I include the sheet it creates)

But the coloring is hard on the eyes. But since I did it - I will post it.

I much appreciate your help.

Thanks
Rasm

[Rasm]

Admin

Just tried your method - Looks like what I need - sweetttttt

I updated the code - and cleaned my code up - then posted it under downloads - I really like the way you did it - great.

Thanks
Rasm