Hi,
how to count how many cells are in color yellow in a huge database that looks as in the attached file?
TRACK_BY_COLOR.JPG
Many thanks.
Gabriele
Hi,
how to count how many cells are in color yellow in a huge database that looks as in the attached file?
TRACK_BY_COLOR.JPG
Many thanks.
Gabriele
Hi,
you can use this code for counting the coloured cells, just update the Range as per your sheet.
Please Login or Register to view this content.
If I've been of help, plz add reputation.
Hi,
Thank you, it works nicely in the worksheet with one color.
what code would work if the database contained more colors and I needed to count only those cells that are color yellow as in the following example:
COLORED DATA.JPG
Thank you,
Gabriele
This UDF can do this.
Please Login or Register to view this content.
Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi Fotis,
when I try to run the macro you suggested, I get the dialog box saying 'Argument not optional'....
Thanks,
Gabriele
Gabriele
As UDF works fine in workbook that i uploaded, i can't imagine what is this that you don't do correctly.
Did you add the code that i suggested to a normal module?
Did you used the columns reference in the UDF correctly?
If so, then i think that you have to upload a small sample workbook WITH my code, to see what happens.
Fotis,
what do you mean by 'normal module'?
Gabriele
Open the Visual Basic Editor which is built into Microsoft Excel by going to Tools->Macro->Visual Basic Editor (or pressing Alt+F11). Add the code.
Look here how to do this.
Thank you for the link. I've got it now right, almost right... That is, for some reason the macro gets the right count for cells in yellow and orange (18 and 14 respectively), but it yields the wrong count for the pink color - it gives 14, when the correct count should be 27... Any idea what went wrong?
Thanks,
Gabriele
I can think that there aren't ALL the "pink cells" the same pink.. Or perhaps you get the pink colour in SOME of the cells using Conditional Formattng..OR.......really i don't know without to see your data.
Hm, it is the same pink (or whatever it's called, I added color by hand in that sample). And the sample data is attached as jpg in my questioned posted earlier today, or do you need it in excel attachement to figure out the problem?
G
An Excel attachement-a WORKBOOK-will be good.
here it is
You haven't added the code to VBA.
Press ALT+F10 in Fotis's attachment and add the coding to your workbook
Apologises, ALT+F11
Ok. You uploaded a sheet without the code BUT this wasn't the problem. The problem was(as i previously mentioned) that were not ALL the cells filled with the same(pink?!) color.
Hi Fotis,
sorry for this, that ugly pink looked all the same to me. It works indeed.
Thank you for solution!
Gabriele
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks