formula
 

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

formula
 

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?

formula
 

With an UDF:

Function ColorCount(rng As Range, colindnum As Integer, fontbackgr As Boolean)
' Application.Volatile
Dim cella As Range
szamlalo = 0
For Each cella In rng
If fontbackgr Then
If cella.Font.ColorIndex = colindnum Then szamlalo = szamlalo + 1
Else
If cella.Interior.ColorIndex = colindnum Then szamlalo =
szamlalo + 1
End If
Next cella
ColorCount = szamlalo
End Function

Usage (e.g. red bacground in range A1:B10):

=ColorCount(A1:B10,3,FALSE)


Regards,
Stefi

€žFrankTLO€� ezt Ã*rta:

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

formula
 

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?

formula
 

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?

formula
 

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?

formula
 

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?

formula
 

Great, but how can I get it to auto recalculate when a cell colour is changed?

"Mike H" wrote:

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?