Excel 2007 : how to get the filled color rgb generated by color scale ?

Hi ererybody,

I apply conditional formatt - color scale to a range in excel 2010, how can I get the background color's rgb vaule through vba?

In Excel 2003, you're limited to the 56-color palette for cell formatting. You can get those by looking at Tools > Options > Color.

This works in 2007, I don't know about 2003, though:

Please Login or Register  to view this content.

Hi liu

Conditional formatting doesn't fill the interior colour a cell, I think it just masks it.

Try this with a cell that has a C/F colour showing

Please Login or Register  to view this content.

I haven't fully tested this yet it might need some adjusting

[EDIT]
This returns the basic colour RGB it doesn't handle the various shades.
Hmmm....???....

Originally Posted by Marcol

Hi liu

[EDIT]
This returns the basic colour RGB it doesn't handle the various shades.
Hmmm....???....

yes, it returns the basic colour RGB , not the various.
Is there any other way?

Hmmm...

Please Login or Register  to view this content.

Hi protonLeah

Please Login or Register  to view this content.

Returns the number of times the Cell/Range has been formatted

For each FCCount there is a ColorScale this has a number of colours, I think 1?,2 or 3 as standard

Please Login or Register  to view this content.

eg
ColorScaleType:=3 Red - Yellow - Blue (Standard Example)

HTML Code: 

Then there are the graded values between each colour!
That's the hard bit, not to difficult if there is a known min/max but if these values are left open that's proving a bit more awkward.

Then there is the possibility of custom setups.....

I'll have to drink deeply in order to grok that!

Have one for me, I had a quiet night in the pub tonight, that's probably why I can't think straight...

I think I'm there with ColorScaleType:=2 gradings, but ColorScaleType:=3 is a bit of a pain, can't get the hang of this additive colour thing....

[EDIT]
Error in post #12 corrected, that's what my problem was I was using the wrong scale....

[EDIT 2]
Sample worksheet attached for ColorScaleType:=3 Red > Yellow > Blue
Might help to explain my rantings

[QUOTE

this is a litle complicated for me .

I have 2 manual ways to change the" color scale " color into steady background color,then I can get the bakcground color's rgb. but I don't know how to do that automaticly.

method 1: copy the " color scale " ranges->goto the target cell->open the Clipboard windows,->choose the right item ->choose "paste". dont use "ctrl+c"and "ctrl+v"

method 2:copy the " color scale " ranges->open a new word file->paste->copy the tabe in word->goto excel->goto the target cell->paste.

the problem is, how to do this automaticly?

Hi liu, sorry about the delay in getting back to you.

Try this demo workbook

Select the range you need to evaluate then run the macro "ReturnRGB_CF"

Please Login or Register  to view this content.

This could be done with a bit of tidying.

The idea of copying to Word was a good one, and that is what this solution does.

I have another solution that doesn't involve Word, but it is not quite there yet, the permutations are a bit awkward, but it will eventually return all the colours for the whole colour scale based on the conditions set, and the max/min values specified.

Hope this helps in the mean time.