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.

Answer 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 = ...