PDA

View Full Version : [SOLVED] Count Cells by Color



Aussiebear
11-05-2014, 07:31 PM
I am using the following function to count cells by color, but comes up with a #name? error.


Function CountByColor(CellColor As Range, CountRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
If ICol = TCell.Interior.ColorIndex Then
CountByColor = CountByColor + 1
End If
Next TCell
End Function


In Cell AH5 I have the function =CountByColor(AF5, tblIndex). Where cell AF5 contains the cell with the color background to count, and tblIndex is the named range K4:AD66. What am I doing incorrectly?

mikerickson
11-05-2014, 07:55 PM
Is tblIndex spelt correctly, (is the third character a lower case L)

Are you sure you didn't enter a =CellColour function.

Its something simple and silly like that probably.


BTW, even with Application.Volatile, changing a cell's color will not trigger the calculation, you either have to enter a value into a cell or manually force calculation to know that the result showing is correct.

Aussiebear
11-05-2014, 08:05 PM
Have checked spelling, and have used the American spelling of color

Aussiebear
11-05-2014, 08:08 PM
BTW, even with Application.Volatile, changing a cell's color will not trigger the calculation, you either have to enter a value into a cell or manually force calculation to know that the result showing is correct.

That will then raise another issue, which I'll raise in a new thread.

mikerickson
11-05-2014, 08:56 PM
That color to calculation issue has no solution. Which is why I rail against using color as a method of input.
There is no VBA solution because changing the color of a cell triggers no events.

Excel is designed for data (numbers and letters) to be input into cells. Color is a visual aid for output only, not a method of input.
(One other disadvantage of using color as data is that the meaning of what color means what increases training time and is non-intuative, for example while Red is the highest importance, is Yellow more important than Orange. Is Blue or Green more important. Isn't 1,2,3,4,5 easier to remember and figure out the first time without subsequent memory error.)

Color can be used to highlight (conditionally formatting a cell depending on if the Import column contains 1,2,3,4 or 5 is one example).
But using it as a method to input data is not wise on any platform (IMO), and defiantly causes issues with downstream calculations.

Bad, bad plan.

Aussiebear
11-05-2014, 09:20 PM
Hmmm.... will 30 mins standing in the corner, thinking bout the errors in my life do?

Kenneth Hobs
11-05-2014, 09:49 PM
Works for me given the color trigger issue which is well known. Perhaps you did not put it into a Module?

Of course a calculation event will recompute the value. A selection event can be a decent trigger to update a bit more often.

Aussiebear
11-05-2014, 10:30 PM
I've placed it in a sheet module for the relevant sheet.

Aussiebear
11-05-2014, 10:39 PM
Now placed it in a module by itself but it returns #Value? error msg

snb
11-06-2014, 01:47 AM
Why not ?


Function F_snb(c00 As Range, c01 As Range)
For Each it In c00
F_snb = F_snb + Abs(it.Interior.Color=c01.Interior.Color)
Next
End Function

It will be triggered every time a calculation takes place if you put =now() or =Today() or =rand() in a cell somewhere in the sheet. It might slow down performance.

SamT
11-06-2014, 02:26 AM
Ted, I just opened the attachment in your other thread, and that is not the code you use in that attachment.

Which I got to work, by assigning the the Function parameter "CountRange" to the private function variable "tblIndex"

The code in post 1 above works, too. As is. :dunno

Both (all) functions were placed in a standard module.

I needed something to trigger the functions for testing, so I used a Selection Change sub on the tblIndex range and "AF5:AH9." I figgerd that you would have to select a cell to change it's color anyway.

Kenneth Hobs
11-06-2014, 06:55 AM
Can you attach a file so we can test as it works for us?

SamT
11-06-2014, 11:14 AM
Kenneth,

I'm not sure that's addressed to me, but I am the only who's shouted "Eureka" so far, and no one has downloaded the attachment in my post #11.

I have been experiencing strange things since the hard drive crashed, including with that post.

Many issues with VBAX server ATT. will try again later

Aussiebear
11-06-2014, 11:18 AM
I've been trying to download your file Sam, since 1.15am (its now 4.12am), but the server keeps telling me that its busy try later, so I don't yet know what you have done.

SamT
11-06-2014, 11:55 AM
It's good to know that you can see that I have an attachment.

Aussiebear
11-06-2014, 12:09 PM
Partial success, thank you Sam. The function correctly counts those numbers allocated to the Lowrance, and not used. Both values for Simrad & Not Loaded (824) are incorrect. I will test further by altering the colours to see if that works.

Kenneth Hobs
11-06-2014, 12:12 PM
The request for attachment was for Aussiebear.

I assumed that you found what I did SamT. When I tried your file, it would not let me copy and paste in that range. When I took that out, it works as I expected.

And yes, lots issues with the forum here lately. Bummer when you make a long reply and it gets lost in server busy land.

raj85
11-10-2014, 01:31 AM
Hi Aussiebear,




I have made below changes in your code :


Public Function CountByColor(CellColor As Range, CountRange As Range) As Integer
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
Dim tblIndex As Range
ICol = CellColor.Interior.ColorIndex


For Each TCell In CountRange
If ICol = TCell.Interior.ColorIndex Then
CountByColor = CountByColor + 1
End If
Next TCell


End Function

and moved code to standard module it works correct also correct all yours formulas use absolute reference for table range ($K$4:$A$D66) so it will not get change when you will drag the formula.

Please find updated attachment.

Aussiebear
11-10-2014, 02:15 PM
Thank you everyone for your valued assistance. I have now altered my approach to this matter by using a helper column and using Countif's on the helper column.

tmogoodie
10-16-2015, 09:33 AM
I will also try.