Count by Color?

[golf4]

Hi, everyone -

I have been expounding the virtues of this website to several people at work, and I swore to one of them that I have seen a posting regarding the ability to count cells by color.

Right now, she has a payroll spreadsheet, using different colors, where she has the cells coded with numbers. The formula she uses counts the cells with red (all coded with "1"s), blue (all coded as "2"s), etc. I remember a posting that, I believe, uses COUNTIF but I can't seem to find it through search. Can anyone help me on this one?

Thanks

 

[dengar]

Many thanks for all the help! I also found this link while looking around the boards:

http://www.mvps.org/dmcritchie/excel/colors.htm

Nice little 56 color index sheet.

Last question: I now have all these lovely formulas within a totals sheet (its counting other sheets by cell colors). The only problem is that the formulas don't automatically update, I have to click in each formula to have the count recheck the values.

I'm sure this is a VERY newbie question, but I'd like to make a button that calls a function to re-calc all my lovely new couonybycolor() functions. I did some searches for this within the forums, but I'm not having much luck - there's SO many messages!


Thanks again,

Mike

 

[dengar]

Got it! Application.Volitile True allows F9 to recalc.

Many thanks for the previous help everyone, this truely is the best excel forum out there.

Thanks,

Mike

 

[Juan Pablo González]

Got it! Application.Volitile True allows F9 to recalc.

Many thanks for the previous help everyone, this truely is the best excel forum out there.

Thanks,

Mike

Yes, but that only calculates the formulas when a calculation happens... which does NOT happen when you change the format in a cell. Just keep that in mind, in case you're getting some *strange* results...

 

[Tom Urtis]

Getting caught up on my involved posts tonight, two follow up points...

One, as Juan said above and I said on March 19, just formatting a cell red (or whatever color) in that range will not update the udf return value, even with...

Point Two, "Application.Volatile True" (notice the corrected spelling of Volatile).

 

[Jak]

I thought I would add this here rather than start a new thread.

Based on the info in this thread I devised a macro to count coloured cells and multiply the count against a 3 criterias. The problem I encountered is that it is not counting cells without a value. I thought it was counting the cell colour but it fails if the cell is blank.

If for example I have three red cells and only one cell contains a value the count returns 1, not 3 and therefore the multiplication thereafter is incorrect.

I need assistance with the count in order that blank cells are counted.

Here's the code:

Sub ValueCountbyColour()

Dim cell As Range
Dim v As Long
Dim m As Long
Dim p As Long
For Each cell In ActiveSheet.Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)
If cell.Interior.ColorIndex = 3 Then: v = v + 1
If cell.Interior.ColorIndex = 4 Then: m = m + 1
If cell.Interior.ColorIndex = 5 Then: p = p + 1
Next
MsgBox "1 x unit Fee £" & v * 7 & vbCr & "2 x unit Fee £" & m * 14 & vbCr & _
"3 x unit Fee £" & p * 19 & vbCr & _
vbCr & "Sum Total = £" & v * 7 + m * 14 + p * 19, , "Invoice Assessor"

End Sub

 

[Jak]

BTT

 

[Juan Pablo González]

I don't see anything strange in there... have you tried stepping through the macro to see where is it failing ?

 

[Jak]

Hi Juan

Yea I did step through it. It runs right through but returns zero if the cells do not contain a value or a space, wierd eh. If I enter a space in a cell then it calculates it so I need to calculate blanks as well as values.

I am using win2k office XP Pro.

 

[Tom Urtis]

The problem could be that you are defining a range based on occupied cells, so cells below the last occupied row won't be included in the color count.

Try replacing the line
For Each cell In ActiveSheet.Range("E1:E" & Range("E" & Rows.count).End(xlUp).Row)

with
For Each cell In ActiveSheet.Range("E:E")

Or if not involving the entire column E, some static last row where no formatting (not just data) would ever be, such as E1:E10000 or wherever. That way, you don't evaluate too many more cells than you need to, but still evaluate all that might contain formatting to get the correct count.

 

[Jak]

Hi Tom

Tried out your suggestion and though it takes a few seconds longer to execute it now calculates correctly and that's the main thing. Good stuff.

Thanks for the assist.