Dear Teylyn,
With due respect to your suggestion and also the earlier solution provided by RoyUK , I definitely would be going through all the nuances..
However, the reason I was asking was I know EXcel formulas better than VBA and have not got a good hand in Excel 2007 as yet.. that's the lacuna....
If the solution is provided on a particular case it becomes easier to absorb..
No intention of ignoring RoyUK's solution at all.
But anyways I will try the solution once again per your instructions...as I was actually looking to understand whether getting Just Colours in a DropDown is possible at all?
I take the words of the learned as the last word as i am no good in VBA but do understand some things in the code which are close to Excel Formulaic Code...
Thanks...
Just found a similar link on ROYUK"s line so thought of just adding it to the thread...
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
You cannot get a colour drop down as you want, maybe with VBA you could use a ComboBox,but it would be complicated.
The Function that I posted achieves what you want
=ColourFunction($AC3,$B$3:$Y$24,FALSE)
Last edited by royUK; 03-27-2010 at 06:23 AM.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
I'm sure that my version should be faster than the other suggestion because it builds a range to calculate instead of calculating each loop. It's actually a slimmed down version of a UDF that I wrote to perform various functions based on Interior or Font Colour - Count,Sum, Average etc. I'll post the full UDF on my web site when I get chance
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Absolutely right! It is indeed faster...though I did not understand the UNION in the VBA code but it does the job pretty well..Originally Posted by royUK
Thanks a lot I will wait for your full & final UDF..
God bless enjoy your weekend...
Dear RoyUK,
It worked however when I tried changing the colour in the Exclusive Cells under Colour DropDown or even in the Coloured Area it remains unchanged..
Is there something that I am missing? Please advise..
You need to recalculate if you change colours, try pressing F9
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Nio i tried that but it did not work even after F9 and pressing enter even if I change the Colours or jusrt put No fill Colour..
The answer remains unchanged..
Then I don't know what you have done it works for me, you need to post the workbook
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Try Ctrl+Alt+F9
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Dear SHG and ROYUK.
Yes it worked after Ctrl+Alt+F9 ..Amazing but cant it be made direct without doing that?
I have not changed anything excpet the Range
ColourFunction($AC2,$B$3:$Y$24,FALSE)
to make it non-absolute..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks