formula

  • Thread starter Thread starter FrankTLO
  • Start date Start date
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
 
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:
 
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'?
 
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
 
Hi
Good so far but how do I specify the colour?


Mike H said:
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
Click to expand...
 
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
 
Back
Top