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
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
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.
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.
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.
