How to get colors made by color scale of conditional formatting of Excel 2012 through VBA code

Question

I need to know: how to get colors made by color scale of conditional formatting of Excel 2010 throught VBA code. Those colors will be subsequently assigned by VBA as chart background according to the following image:

www.lnkm.cz/Slozka/Example.jpg http://www.lnkm.cz/Slozka/Example.jpg

I did a research on various web sides and:

1. Most people advice how to read color of conditional formatting by method <Cell>.FormatConditions(index that is active).Interior.ColorIndex but in my case it don’t work because of error “Object doesn’t support this property or method”
2. Some people advice to write own computation of colors (based on cells value). I found various ways how to do it, but none of them can compute same colors as was computed previously by excel (same colors as are on previous picture).

So I’m asking:

1. Is there any way to directly ready colors from cells? (or those colors are not accessible for API)
2. Do you know how to compute same colors as excel compute?
3. Do you know any other way how to solve my problem?

I believe that it has to work somehow.

Solution 1

if no better answer is provided, you can try this workaround:

1. link / copy your data to cells under the chart (with formulas like =Sheet1!A1)
2. apply the same conditional formatting
3. hide the values (with custom number format like "", i.e. empty string literal (2 double quotes))
4. make the chart transparent
5. align the cells with the chart

---

UPDATE:

or you can try to compute the color by linear approximation for each R, G, B channel if the conditional format uses only 2 base colors (r1, g1, b1) and (r2, g2, b2) for 2 corner cases which can be

• min and max value, e.g.: 0 - 4 000
• min and max percent, e.g.: 10% - 90%
  (i believe you can use % * [max_value - min_value] to get the actual value)
• min and max percentile, e.g.: 0th percentile - 100th percentile

for percent / percentile options you first need to convert an actual value to the percent / percentile value, then if value < min or value > max use the corner colors, otherwise:

r = r1 + (r2 - r1) * (value - min_value) / (max_value - min_value)
g = ...
b = ...

Solution 2

This is not specific to your problem but is easily modified to solve your problem...

Sub CopyCondFill()
    Dim FromSheet As Object
    Dim ToSheet As Object
    Dim FromSheetName as String
    Dim ToSheetName as String
    Dim ToRange As Range
    Dim StrRange As String

    '''Sheet with formatting you want to copy
    FromSheetName = "YourSheetsName"
    Set FromSheet = Application.ThisWorkbook.Sheets(FromSheetName )
        '''Start of range within sheet you want to copy
        FromFirstRow = 3
        FromFirstCol = 2

    '''Sheet you want to copy formatting to
    ToSheetName = "YourSheetsName"
    Set ToSheet = Application.ThisWorkbook.Sheets(ToSheetName)
        '''range to copy formatting to
        ToFirstRow = 3
        ToFirstCol = 2
        '''NOTE: Adjust row/column to take lastrow/lastcol from or enter value manually
        ToLastRow = FromSheet.Cells(Rows.Count, 1).End(xlUp).Row
        ToLastCol = FromSheet.Cells(2, Columns.Count).End(xlToLeft).Column
        Set ToRange = ToSheet.Range(Cells(ToFirstRow, ToFirstCol), Cells(ToLastRow, ToLastCol))

        '''Apply formatting to range
        For Each cell In ToRange
            StrRange = cell.Address(0, 0)
            ToSheet.Range(StrRange).Offset(ToFirstRow - FromFirstRow, ToFirstCol - FromFirstCol).Interior.Color = _
                FromSheet.Range(StrRange).DisplayFormat.Interior.Color
        Next cell

End Sub

Solution 3

This will copy a picture of a cell to the top-left corner of a chartobject on the same worksheet. Note the picture is linked to the copied cell - if the value or formatting color changes it will change to match.

Sub Tester()

    CopyLinkedPicToPlot ActiveSheet.Range("E4"), "Chart 2"

End Sub

Sub CopyLinkedPicToPlot(rngCopy As Range, chtName As String)

    Dim cht As ChartObject

    Set cht = ActiveSheet.ChartObjects(chtName)

    rngCopy.Copy
    With rngCopy.Parent.Pictures.Paste(Link:=True)
        .Top = cht.Top
        .Left = cht.Left
    End With

End Sub

EDIT: I just tested this with a fairly small 4x8 matrix of cells/charts and the performance is pretty bad! Might be better just pasting without Link:=True ...

Solution 4

Try this:

<Cell>.DisplayFormat.Interior.Color

This should word with Excel later than 2010.

View more solutions

Comments

[Jure]

Hi deathApril, nice idea, but I simplify explanation in my question. In the fact I need to add in to each left top corner of every chart small rectangle (shape in excel terminology) with color of conditional formating and its value. If it will be backgroud of chart, it makes imposible to observe chart points of dark purple cases. but you idea was inspirating.

[Aprillion]

if you had your chart white except the small rectangle areas in left top corner shapes... but no, it won't work for retrieving 'its value' :(

[Aprillion]

i updated my answer, but the computation is just my educated guess, i don't have excel here to test it..

[Tim Williams]

I think that Excel 2007 is less prone to snapping colors to the pallette than prior versions. Eg: see databison.com/index.php/…

[Tony Dallimore]

@Tim. Thanks for the information, I will have to try. I performed the experimentation that resulted in the above list seven or eight years ago and I have never had reason to revisit it. Since I am so careful to stick to this list, I had not noticed that Excel 2007 was better at handling colours than 2003. Always good to learn something new. I am also pleased that a list of Excel's colours is available on the internet now; there was nothing similar that I could find when I needed it.

[Jure]

Ive try this solution, but there is problem with performance. For my purpose I need change parameter with name "NPHR" (orange one on previous picture) and observe changes in "purple formated area" and "group of charts" (on left and righr part of picture). Both contains huge amout of data (excel file has 90 MB) and one change of parameter NPHR takes 0,5s second for "purple formated area" and 2,5s for "group of charts". Once I copy just one cell as yoiu advice it takes cca 6 seconds, I gues that it will be definetly wery slow for all necesary cells.

[Jure]

Ive try your update. Colors are not same than excel condition formatting. But the bigger matrix, less obvious difference in colors. I’ll try if it acceptable for my purposes and let you know if it is solved.

[J-Dizzle]

@Tony where'd you find this list? source?

[Tony Dallimore]

I created my list by experimentation. I coloured cells and then read Cells.Interior.Color. If you search the web for "Excel colour palette" you will find any number of sites containing this information,