get the cell background color with VBA (with conditional formatting)

[Lolo]

Hello,

I have created a basic color scale with the conditional formatting Excel(2010) function (with 3 colors).
And now I want in VBA to loop on the cells of the "color scale" in order to get each (background) color value (in order to use the color on shapes)

But I only get -4142 value for each cell.
OK, it is because the cell background has actually, no color, it is the format condtion which applies color. So I need for each cell to take over the formatCondition object and get the interior.color or interior.colorindex property...
but it seems this is not possible... The program fails with error like 'not right property,etc...'

Have you any idea on how to do this ?

NB: I have found a solution by using a copy of the color scale... in Mirosoft Word, and a paste back in Excel (!!),
http://www.excelforum.com/excel-gen...led-color-rgb-generated-by-color-scale-2.html
it works great, but i'm wonderging if there was another more elegant solution...

Thank you for your help.

 

[NARAYANK991]

Hi Lolo ,

See if this helps :

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

Narayan

 

[Lolo]

Thank you for your suggestion.

However i had already seen this post (i have made several Research on this subject)
Not working because i use a scale color(3color scale), and i am in excel 2010.

I have try to use it in order to better manipulate the FormatConditions object and color properties, but it is not working, for sure because of using a color scale.

It seems it is not possible to get the conditional formatting color of a specific cell within the range of the scale color

Any Other suggestion ?

 

[eibi]

Lolo,

What a headache! I recently had the same challenge, and found (like you) that there isn't an easy solution. I was glad to see your post - albeit a few months too late to be of much help to you, I suppose.

Here's my proposal: I discarded the idea of tying to the CF color, and instead I put together a series of 3 helper cells which calculate values for R, G, and B respectively -- and then ran those values through VBA to color the target chart object.

Sub ColorChart()

' Color the chart in the report based on a calculated percentage

Dim r As Integer
    r = Sheets("Sheet1").Range("D24").Value

Dim g As Integer
    g = Sheets("Sheet1").Range("D25").Value

Dim b As Integer
    b = Sheets("Sheet1").Range("D26").Value

Sheets("Sheet1").ChartObjects("Chart 2").Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(r, g, b)

End Sub

It's fairly rudimentary; but I needed a quick solution, and I think this will do it for now.

See attached.

 

[Chihiro]

Hi Eibi,

You might find this useful.
http://www.cpearson.com/Excel/CFColors.htm

 

[eibi]

Thanks for the link.

It's similar in many ways to the link NarayanK suggested earlier in the thread -- On one hand it's very helpful: It introduced me to some aspects of VBA that I haven't encountered before...and, with this new information, I was able to pickup Conditional Formatting color of a cell and apply it to the chart object. But: Only if the Conditional Formatting rule is formula-based.

This is my code:

Dim ColorOfCF As Variant

ColorOfCF = Sheets("Sheet1").Range("$C$21").FormatConditions(1).Interior.Color

Sheets("Sheet1").ChartObjects("Chart 2").Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = ColorOfCF

This code works if my Conditional Formatting is something like: = Cell Value > 0

But this code fails if my Rule is a Graded Color Scale; it throws an Error 438 "Object doesn't support this property or method"

But I've been using VBA for less than a year, so perhaps I've missed something?

 

[Chihiro]

Unfortunately, there are certain caveat.

1) CF can't contain relative reference
2) Cell Value Is can't be used. You can however, re-write your CF with Formula instead of Value Is.