10 replies to this topic

[manicd]

Hi

I was wondering if anyone could help me with counting the number of cells filled with a colour in Excel.
I've googled the question which has provided many answers, but I just don't understand them. This is an example of one of these sites HERE.
What I'm trying to do is count the number of cells which are filled Yellow & Grey in each column lettered C - AY;

What I do understand is that you need to use the VBA and insert a Module containing a formula that will perform this for me. I also need the formula to automatically update when I enter a colour in to a cell.
I know this is a big ask but if anyone can at least guide me on doing this it would greatly appreciated.

Edited by manicd, 13 September 2009 - 11:08 AM.

[manicd]

Anyone please. There must be some Macro wizards out there who can help me.

Edited by manicd, 15 September 2009 - 12:04 PM.

[Glaswegian]

Hi there

Chip Pearson's method is the best - and the one I would have pointed you towards had you not already found it.

However, if it's just a simple count by colour you need, then you could try this previous version of Chip's function. It doesn't include any other functions - just the simple count. And you don't need to download the module. I've used this in the past and it works just fine.

From Excel, press Alt+F11 to open the VBE. Add a new module and copy and paste the following into the module

Function CountByColor(InRange As Range, _
	WhatColorIndex As Integer, _
	Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim rng As Range
Application.Volatile True

For Each rng In InRange.Cells
If OfText = True Then
	CountByColor = CountByColor - _
			(rng.Font.ColorIndex = WhatColorIndex)
Else
	CountByColor = CountByColor - _
	   (rng.Interior.ColorIndex = WhatColorIndex)
End If
Next rng

End Function

This is the function that does the counting.

Of course, you need to know the index number of each colour. To find the number, copy this code and run it on another sheet

Sub ListColours()
Dim x As Integer

For x = 1 To 56
	ActiveSheet.Cells(x, 1).Interior.ColorIndex = x
Next
End Sub

This will list all the colours available - just read off the colour number against the row number. For example, yellow = 6.

Now you can insert the formula in your total row
=CountByColor(C1:C5,6,FALSE) or use Insert Function to choose from the User Defined List.

The function should re-calculate automatically as it uses Application.Volatile - you'll probably need to click in another cell to ensure it actually does it.

Any problems just post back.

[manicd]

Hi Glaswegian & thanks that seems to have done the trick, cheers.

[manicd]

Hi Glaswegian

Just one more question.
How do I count two separate colours in the same column with the =CountByColor(C1:C5,6,FALSE) formula?
Also is there anything I can put in to the formula so I don't have to keep clicking on the total's row when I add a colour?

Edited by manicd, 19 September 2009 - 11:39 AM.

[Glaswegian]

Hi I'm not aware of any way of doing that. Perhaps you would need to put the functions in a VBA routine and then output to the sheet. Because counting colours is not a native Excel function, the one I posted earlier, and the others on Chip Pearson's site are a way around Excel's limitations. The same applies for your second question - even if you were using code to do this, you would need the colorindex value - which you would have to include in your code. So any changes would require code changes. I appreciate it's not very efficient. An alternative would be to look again at the way your data is presented. Is a coloured cell the only way? The beauty of Excel is that there are often many different ways to reach the same result.

[manicd]

Thanks Glaswegian for all your help & suggestions

[Juste]

i dont know, but i cant make it work... it always shows error in formula.... tired of trying... why is that?... can someone help me , please?......

[Glaswegian]

Hi and welcome. Can you give me some more information? Where is the error? What error message do you receive? The more information you provide, the better chance we have of working out a solution.

[Juste]

it says: 'the formula you typed contains an error.' but i think i did everything correctly: Alt + F11, then the code, saving with Alt + Q, then typing formula and it doesnt recognize.... i have tried different codes (i found on the internet), differen formulas, but nothing.... it always shows error... thanks.

[Glaswegian]

Hi That often indicates a missing bracket, or a bad reference or a missing comma etc. Can you post the formula you have that is showing the error?