| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula
How can I count in a range of cells the cells that have a particular fill
colour? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
