Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Couting based on color and Filter
hi
I have a data more than 1000 rows, most of them highlited with different colours, my question is: Is it possible to count based on specific highlited colours for example yellow= 50 row, blue= 70, red =105. In addition to this, in excel-2003 the auto-filter does not display more that 1000 row, is it have any solution thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Couting based on color and Filter
You need to create VB functions for working with colours. Copy these into a
VB Module ALT + F11, Insert Module then paste: This returns the index number of the cell in the reference. Use this number tin the following functions. Function cellColorIndx(ByVal ref) As Variant If ref.Interior.ColorIndex = xlNone Then cellColorIndx = "No Cell color" Else cellColorIndx = ref.Interior.ColorIndex End If End Function Function sumColour(ByVal ref As Range, ind As Integer) 'Add the values of the cells with the same index number Dim c For Each c In ref If c.Interior.ColorIndex = ind Then If IsError(c) Or Not IsNumeric(c) Then sumColour = sumColour Else sumColour = sumColour + c End If End If Next End Function Function CountColour(ByVal ref As Range, ind As Integer) ' Count the cells formatted with the index number Dim c For Each c In ref If c.Interior.ColorIndex = ind Then If IsError(c) Or Not IsNumeric(c) Then CountColour = CountColour Else CountColour = CountColour + 1 End If End If Next End Function Perhaps someone else can answer the filter question, But I seem to remember that www.Contextures.com has an article on it where you use sucessive filters to narrow the range Regards Peter "Aslam" wrote: hi I have a data more than 1000 rows, most of them highlited with different colours, my question is: Is it possible to count based on specific highlited colours for example yellow= 50 row, blue= 70, red =105. In addition to this, in excel-2003 the auto-filter does not display more that 1000 row, is it have any solution thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advance Filter can be based on cell Color in Microsoft Excel | New Users to Excel | |||
Couting the number of referrences that... | Excel Worksheet Functions | |||
couting based on age from today | Excel Discussion (Misc queries) | |||
couting transactions for same time | Excel Worksheet Functions | |||
How do I filter based on color in Excel? | Excel Worksheet Functions |