Good so far but how do I specify the colour?

You go back to my first post where I wrote:-

Where A1:A20 is the range to count and B1 is the colour you want to count.
Note this won't work for conditional format colours, you can do that but it's
much more complicated.

You set the colour of b1 to the colour you want to count.

Mike

"FrankTLO" wrote:

Hi
Good so far but how do I specify the colour?


"Mike H" wrote:

Hi,

After ALT+F11 you are in the VB Editor. On the left you should see 'Project
Explorer' and if you don't press CTRL+R. In projetc explorer you will see you
workbook name and underneath that 'This Workbook'. Right click this workbook
anf in the popup click 'insert module' and you will see a blank white space
on the right. Paste the code in there.

Mike

"FrankTLO" wrote:

Sorry,

I got a far as
'You need a UDF for that. Alt+F11 to open VB editor'
but where do I
'Right click 'ThisWorkbook' and insert module and paste the code'?



"Mike H" wrote:

Hi,

You need a UDF for that. Alt+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code below in.

Call with

=CountByColor(A1:A20,B1)

Where A1:A20 is the range to count and B1 is the colour you want to count.
Note this won't work for conditional format colours, you can do that but it's
much more complicated.

Function CountByColor(Rng As Range, ClrRange As Range) As Double
Dim c As Range, TempSum As Double, ColorIndex As Integer
Application.Volatile
ColorIndex = ClrRange.Interior.ColorIndex
TempSum = 0
On Error Resume Next
For Each c In Rng.Cells
If c.Interior.ColorIndex = ColorIndex Then
TempSum = TempSum + 1
End If
Next c
On Error GoTo 0
Set c = Nothing
CountByColor = TempSum
End Function


Mike

"FrankTLO" wrote:

How can I count in a range of cells the cells that have a particular fill
colour?