Results 1 to 6 of 6

Thread: Correlation Map with color codes

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    13

    Correlation Map with color codes

    All
    I have attached a file with an extract of my code
    Attached Files Attached Files
    xl2007 - Windows 7
    xl hates the 255 number

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Rasm,

    See this couple of links:

    1. AJP Excel Information - Progress meters

    2. Excel
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    13
    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
    Last edited by Rasm; 12-04-2011 at 06:48 PM.
    xl2007 - Windows 7
    xl hates the 255 number

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi,

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

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

    Code:
    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).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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    13
    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
    Attached Files Attached Files
    xl2007 - Windows 7
    xl hates the 255 number

  6. #6
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    13
    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
    Last edited by Rasm; 12-04-2011 at 09:28 PM.
    xl2007 - Windows 7
    xl hates the 255 number

Similar Threads

  1. Color Functions In Excel
    By Admin in forum Download Center
    Replies: 2
    Last Post: 10-24-2013, 11:44 AM
  2. Heat Map
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 1
    Last Post: 01-02-2013, 02:57 PM
  3. World Map Excel Chart
    By S M C in forum Download Center
    Replies: 12
    Last Post: 07-23-2012, 10:04 AM
  4. Find Color in the string using formula.
    By LalitPandey87 in forum Excel Help
    Replies: 4
    Last Post: 07-10-2012, 09:16 PM
  5. AutoFilter by Multi Color
    By Admin in forum Download Center
    Replies: 0
    Last Post: 09-08-2011, 07:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •