Tough Excel question

[demon42]

I have a very large data tabe in excel for which I am trying to calculate a completion percentage. A number of the cells have been grayed out due to hardware issues.

How can I count how many shaded cells there are in a range of cells without counting them each myself?

 

[Whitecloak]

I dont think this is possible without writing code in VBA. AFAIK, there is no ready made excel function to return the color value of a cell.

 

[demon42]

I was worried about that

I'm not experienced with VB at all, can anyone help?

 

[KLin]

check out this article


1. click tools
2. click macro
3. click visual basic editor
4. right click microsoft excel objects
5. click insert, click module
6. copy and paste the first 2 functions that are listed in this link
7. make a cell the same shading as the cells you want to count
8. copy and paste this into another cell:

=countbycolor(A$1:A$17,cellcolorindex(A$3,0))

change the A$1:A$17 to the range you want to use, and change the A$3 to the cell that you created in step 7.

 

[demon42]

Thank you, that's exactly what i'm looking for!
...unfortunately for some reason all I get is #NAME? instead of a value...
It seems like I'm missing a step ...maybe in applying the VB to the workbook? It seems like it is already... but otherwise I dont know how to explain the error!
What should I try to fix this?

 

[demon42]

I got it!
I'm sorry, I didn't have the functions in a module!

my fault, thank you so much for the help!!!

 

[KLin]

Originally posted by: demon42
I got it!
I'm sorry, I didn't have the functions in a module!

my fault, thank you so much for the help!!!

No problem. I forgot to add "into the created module" in step 6 🙂.