Possible to retrieve fill color value from a cell with VBA?

[largeselection]

Hi,

I am using excel 2007+ and I have a list of 200 contributors along with their annual contributions. I have conditionally formatted the contributions using the "Color Scale" conditional format from Red (lowest) to Green (highest).

Is there anyway to communicate this fill color to excel vba? I'm trying to use the colors to highlight other portions on the worksheet so I want the colors to match.

It doesn't seem that any of the other methods of identifying the fill color work so I'm guessing it has to do with the fact that I'm using the color scale conditional formatting?

Any ideas/links/suggestions are much appreciated.

Thanks!

 

[largeselection]

So I found that if I copy the range with the color scale conditional formatting and paste it from the clipboard it pastes it as a regular fill!

I hit record to see what came out when I did that, but the code that it recorded doesn't work at all.

This is what it recorded:

 Range("G27").Select
    ActiveWindow.SmallScroll Down:=28
    Range("G27:G74").Select
    Selection.Copy
    Range("H27").Select
    Application.CutCopyMode = False
    ActiveSheet.Paste

Any ideas how to fix that? Because then I could potentially just add this section and pull the color from the newly pasted cells.

 

[Tom Urtis]

If you want the colors of other cells to match, you need to format them with the same conditions that led to the color being returned by the formula rule in the list of 200 conditionally formatted cells. What you may or may not know is, conditional formatting does not change the actual interior color property of a cell; it only *looks* as if it does. Really, no matter what color conditional formatting results in, the actual underlying color of that cell is still unchanged. That is why you cannot detect the CF color with VBA, because the color is still what it always was, not what it looks like. There is a very complicated method to evaluate CF formats programmatically but with 2007 and 2010's huge list of colors, it would be a bad and probably unsolvable approach. Gets you back to the matching of conditions instead of the matching of un-evaluate-able colors.

 

[largeselection]

I did not know that fact.

Thank you for your insight. I was hoping there might be some sort of workaround, but it seems I will have to try a different approach.

Thanks!