Count cells in a range that have same bacgoround color

[tavaritz]

Hello.

Is there any way to count how many cells in a range has the given background color?

I'm using Excel 2000.

Topi

[AndyLitch]

Always a way but may i ask why the cells have a different colour?

[tavaritz]

I've used Excel to record a collection of mine and I use backround color to grade the quality of the items. Default color is one that I don't yet have, dark green is mint, light green is near mint, yellow is excelent, orange is good and red is poor condition.

Now I want to calculate the number of items I'm still missing eg. all cells with default backcolor or the number of poor items eg. all cells with red background. I have about 7000 items I own and I estimate that I'm missing about 5000 items, but I want to double check those numbers with my collection and I cannot do it with ease if I cannot calculate the numbers.

Topi

[HaHoBe]

Hi, tavaritz,

with Excel2000 you may only use a UserDefinedFunction (UDF) which receives the range and the number of the color that you want to check (or macro code which may update while you enter values, color changes will not trigger any event).

Ciao,
Holger

[tavaritz]

To elaborate a little bit.

I have separated the entries to separate tables based on the first character of the title. On each table the first column has the name of the title and second and cosequent columns has the print run information and it's this print run information that I color code. This is because for certain item I can heve second and third printing with second in good condition but the third in mint and I'm still missing the first one.

It's easy to me to count how many titles there are and how many individual printings there are, but how to calculate how many mint condition printings I have is what I don't know.

Topi

[tavaritz]

Hi, tavaritz,

with Excel2000 you may only use a UserDefinedFunction (UDF) which receives the range and the number of the color that you want to check (or macro code which may update while you enter values, color changes will not trigger any event).

So, how do I do that?

Topi

[AndyLitch]

The reason I asked about the colour was to highlight the fact that you manually set the record/row colour using your personal criteria... Ideally in a database, you would have an extra field classifying the record by the criteria you set... As a simple example 1-5... Excel's conditional format could then use that field to set the colour accordingly... The main point being that colour is more of a visual aide for people than for the machine... That's just a point for future reference
The second point about database design is that you don't need to physically separate your data.. You can throw all of your data into a single contiguous table and Excel has a number of built in tools like autofilter and pivot tables to help you extract the specific displays you need using field criteria.

I would suggest you redesign your database slightly and I can help you automate that with code ... Once your database design is optimised we can unleash the full power of Excel ............ But it's your choice

[tavaritz]

The reason I asked about the colour was to highlight the fact that you manually set the record/row colour using your personal criteria... Ideally in a database, you would have an extra field classifying the record by the criteria you set... As a simple example 1-5... Excel's conditional format could then use that field to set the colour accordingly... The main point being that colour is more of a visual aide for people than for the machine... That's just a point for future reference
The second point about database design is that you don't need to physically separate your data.. You can throw all of your data into a single contiguous table and Excel has a number of built in tools like autofilter and pivot tables to help you extract the specific displays you need using field criteria.

I would suggest you redesign your database slightly and I can help you automate that with code ... Once your database design is optimised we can unleash the full power of Excel ............ But it's your choice

I already have three dimensions in my table: Alphabetical, Title and Print run. Where would I put the condition code and how could I access it so that I know what title/printrun it applies to?

It's also very hard for me to use Excel's automated tools as they don't alphabetize text strings correctly. On my record colection I've used separate alphabetization field for each entry and adding new entries is a mess. But I buy new records only one in a month whereas I could get hundreds of items to this collection in a day.

Topi

[HaHoBe]

Hi, Tobi,

if you enter digits/characters you could the worksheetfunction CountIf to get the result.

This function goes into a standard module and may get called from the function wizard:

Please Login or Register  to view this content.

It will need to ranges: the range you want to sum up, and the cell with the background color. If you change the color of any cell this function wil not update automaticly as any change of color will not trigger an event that might get caught by Excel (but any volatile function like Now() does).

Ciao,
Holger