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?
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?
Last edited by liu; 09-12-2010 at 08:03 AM.
In Excel 2003, you're limited to the 56-color palette for cell formatting. You can get those by looking at Tools > Options > Color.
Entia non sunt multiplicanda sine necessitate
This works in 2007, I don't know about 2003, though:
Please Login or Register to view this content.
Last edited by protonLeah; 09-10-2010 at 08:25 PM.
Ben Van Johnson
Interesting....
I am using this
Please Login or Register to view this content.
In most cases it entirely agrees with protonLeahs' solution
I have however found this exception
My code returns what seems to be the correct result but PLs' apparentlly cannot differentiate the two.Please Login or Register to view this content.
Attached is a demo "custom randomized palette" workbook in 2007 (work still in progress)
shg、protonLeah、Marcol -
thanks for your reply.
I use excel 2010, the code can return the manually filled background color , but can't return the background color generated by "color scale".
If you use 2010 and not 2003 would you please update your profile to say so.
It can make a big difference to the solution you get offered.
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....???....
Last edited by Marcol; 09-11-2010 at 12:12 PM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Hi liu
Please don't quote whole posts in your replies, this is not necessary, it just clutters the board and the moderators don't like it.....
I'm working on it, but haven't found a solution yet. Keep looking in and hopefully I or someone else will have a solution soon.
Hmmm...
Please Login or Register to view this content.
Hi protonLeah
Returns the number of times the Cell/Range has been formattedPlease Login or Register to view this content.
For each FCCount there is a ColorScale this has a number of colours, I think 1?,2 or 3 as standard
egPlease Login or Register to view this content.
ColorScaleType:=3 Red - Yellow - Blue (Standard Example)
Then there are the graded values between each colour!HTML Code:
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.....
Last edited by Marcol; 09-12-2010 at 10:09 PM. Reason: Red - Yellow - Blue corrected to Red - Yellow - Blue
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
Last edited by Marcol; 09-12-2010 at 10:48 PM.
[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?
Last edited by liu; 09-13-2010 at 10:14 PM.
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.
Last edited by Marcol; 09-14-2010 at 11:22 PM.
Hi Marcol,
did you manage to develop an alternative to word copy/paste? If so would you mind sharing it?
TX
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks