Using property ColorScaleCriteria color you cells

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

Code:

---

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.Count).SetFirstPriority
        .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
        With .FormatConditions(1).ColorScaleCriteria(1).FormatColor
            .Color = 7039480
            .TintAndShade = 0
        End With
        .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
        .FormatConditions(1).ColorScaleCriteria(2).Value = 50
        With .FormatConditions(1).ColorScaleCriteria(2).FormatColor
            .Color = 8711167
            .TintAndShade = 0
        End With
        .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
        With .FormatConditions(1).ColorScaleCriteria(3).FormatColor
            .Color = 8109667
            .TintAndShade = 0
        End With
    End With

End Sub

---

Thanks
Rasm