none
(Excel+VBA) Conditional Formatting - ColorScale - 3 color gradient

    Question

  • Ihave questions about the colorscale. I have selected a range of cells for which the I created a 3 color scale for the numbers. Then while copying the format of the top cell to the another cell, the color does not get copied. Instead it is copying some other color. My question is how do I color the neighboring cells with the same color (or how do I reference the color from a colorscale range of cells) Below is the code:

     

    Range("M3:M20").Select
    
    Selection.FormatConditions.AddColorScale ColorScaleType:=3 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue 
    
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor 
    .Color = 7039480 
    .TintAndShade = 0 
    End With 
    
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile 
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50 
    
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor 
    
    .Color = 8711167 
    .TintAndShade = 0 
    
    End With 
    
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue 
    
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor 
    .Color = 8109667 
    .TintAndShade = 0 
    End With 
    
    Range("M3").Select 
    
    Application.CutCopyMode = False 
    
    Selection.Copy Range("K3").Select 
    
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 


    • Edited by aagar_2003 Monday, October 10, 2011 2:34 PM more code format
    • Edited by danishaniModerator Tuesday, January 31, 2012 1:09 AM reformat code for readability_
    Sunday, October 09, 2011 3:47 PM

Answers

  • Sorry I don't know how to post the code. The forum just gives the check option of whether code is included or not.

    There is a way but simply break the code into individual lines, paragraphs help too.

    About your response, all you are trying to say is I need to replicate the algorithm for the ColorScale object. However, that is not helpful.

    Indeed the only way to get the CF colours is to replicate the algorithm. IOW, there is nothing in any object that will tell you want the colours are, at least not directly. The algorithm is straightforward, simply a matter of "scaling" the cell values between the respective min/mid/max points, and scaling the colour differences pro-rata.

    If I am using a range of cells and performing a conditional formatting, the ColorScale object should have various members that I should be able to extract.
    May be there is an array of values for each cell. Now I need to know to reference that array and extract the values from that ColorScale object of a particular cell.

    There certainly are, here are a few things you can look at -

    Set rng = one or more cells in the color scale
    Set rngAppliesTo = rng(1).FormatConditions(1).AppliesTo
    rngAppliesTo will refer to the entire range of cells in the colorScale, from which you can return the cell values

    Dim fCS As ColorScale
    Set fCS = rngAppliesTo.FormatConditions(1)

    Then loop like this
    for i = 1 to 3 ' assuming 3 colours
    fCS.ColorScaleCriteria(i).Type
    fCS.ColorScaleCriteria(i).Value ' could be a formula to evaluate)

    Get respective min/mid/max values, check the .Type and as I mentioned before you'll need a bit of maths to convert if e Percentile or Percent

    Also you can get the respective colours as I mentioned before in the same loop.
    fCS.ColorScaleCriteria(i).FormatColor.Color

    However all this only give you the variables required in replicating Excel's colorscale algorithm. Once again to be clear, there is no direct way to get the CF cell colours from the ColorScale object. I appreciate that's not the answer you are looking for but it is the correct answer.

    Actually in theory could get the pixel colours off the screen, but more effort than calculating the colours.

    Peter Thornton

    Monday, October 10, 2011 3:14 PM
    Moderator

All replies

  • It's difficult to decipher your code the way you have posted it, however to get the colours in a CF colorscale means essentially replicating the calculations that go on behind the scenes to produce the colours. Briefly -

    1 Determine the min/mid/max values (calculate percentage or use the percentile function if/as appropriate or other lowest/highest etc). These may need more adjusting if they are not logical order in respect of the data, eg the mid is less the min, or min is more than max, etc (it's complicated if not in logical order)

    2 Get the min/mid/max colour values (that's easy)

    3 Calculate and "scale" the cell values between, <= min, min to mid, mid to max, >=max
    Linearly calculate the RGB differences between the min-mid or mid-max colours.

    Peter Thornton

    Monday, October 10, 2011 12:46 PM
    Moderator
  • Sorry I don't know how to post the code. The forum just gives the check option of whether code is included or not. About your response, all you are trying to say is I need to replicate the algorithm for the ColorScale object. However, that is not helpful. If I am using a range of cells and performing a conditional formatting, the ColorScale object should have various members that I should be able to extract. May be there is an array of values for each cell. Now I need to know to reference that array and extract the values from that ColorScale object of a particular cell.
    Monday, October 10, 2011 2:24 PM
  • Sorry I don't know how to post the code. The forum just gives the check option of whether code is included or not.

    There is a way but simply break the code into individual lines, paragraphs help too.

    About your response, all you are trying to say is I need to replicate the algorithm for the ColorScale object. However, that is not helpful.

    Indeed the only way to get the CF colours is to replicate the algorithm. IOW, there is nothing in any object that will tell you want the colours are, at least not directly. The algorithm is straightforward, simply a matter of "scaling" the cell values between the respective min/mid/max points, and scaling the colour differences pro-rata.

    If I am using a range of cells and performing a conditional formatting, the ColorScale object should have various members that I should be able to extract.
    May be there is an array of values for each cell. Now I need to know to reference that array and extract the values from that ColorScale object of a particular cell.

    There certainly are, here are a few things you can look at -

    Set rng = one or more cells in the color scale
    Set rngAppliesTo = rng(1).FormatConditions(1).AppliesTo
    rngAppliesTo will refer to the entire range of cells in the colorScale, from which you can return the cell values

    Dim fCS As ColorScale
    Set fCS = rngAppliesTo.FormatConditions(1)

    Then loop like this
    for i = 1 to 3 ' assuming 3 colours
    fCS.ColorScaleCriteria(i).Type
    fCS.ColorScaleCriteria(i).Value ' could be a formula to evaluate)

    Get respective min/mid/max values, check the .Type and as I mentioned before you'll need a bit of maths to convert if e Percentile or Percent

    Also you can get the respective colours as I mentioned before in the same loop.
    fCS.ColorScaleCriteria(i).FormatColor.Color

    However all this only give you the variables required in replicating Excel's colorscale algorithm. Once again to be clear, there is no direct way to get the CF cell colours from the ColorScale object. I appreciate that's not the answer you are looking for but it is the correct answer.

    Actually in theory could get the pixel colours off the screen, but more effort than calculating the colours.

    Peter Thornton

    Monday, October 10, 2011 3:14 PM
    Moderator