Using property ColorScaleCriteria color you cells

[Rasm]

All

With help from Admin (he did the color part) - I have now a good example of how to use colors to aide the understanding of data.

This example makes a Correlation Map - so using the worksheetFunction Correlation a matrix of R values are calculated and placed in a sheet created by the code. Since an R has a scale from -1 to plus 1 - the color shades are now added - producing a correlation map that is easy to read.

From the sheet("WorkSpace") - simply load the userform and create the map by pressing <Create Map>.

Also note the code that turns off the event handler in 'ThisWorkBook' - if you trap event(s) of what the user does manually on the sheet - then make sure to turn off the eventHandler - otherwise it will take a lot longer to fill the sheet. Again Thanks Excel Fox for showing me how to turn of the eventHandler.

Again - Thanks to Admin for doing the color part - I was stuck - hope somebody else can find use for this.

Code that adds color by Admin - I remmed out one line that gave me an error


Public Sub AddColor2Sheet()
With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
ColLast = .Columns(.Columns.Count).Column
End With
With ActiveSheet.Range("$B$2:$" & Split(Cells(1, ColLast).Address, "$")(1) & "$" & LastRow)
.FormatConditions.Delete
.FormatConditions.AddColorScale ColorScaleType:=3
'.FormatConditions(Selection.FormatConditions.Coun t).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

End Sub



Thanks
Rasm