Couting based on color and Filter

[Aslam]

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

 

[Billy Liddel]

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