Count Cells by Color

[Aussiebear]

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]

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]

Have checked spelling, and have used the American spelling of color

[Aussiebear]

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]

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]

Hmmm.... will 30 mins standing in the corner, thinking bout the errors in my life do?

[Kenneth Hobs]

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]

I've placed it in a sheet module for the relevant sheet.

[Aussiebear]

Now placed it in a module by itself but it returns #Value? error msg

[snb]

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]

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.

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]

Can you attach a file so we can test as it works for us?

[SamT]

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]

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]

It's good to know that you can see that I have an attachment.

[Aussiebear]

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]

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]

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]

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]

I will also try.