Count, Sum, and Filter by Color

[Khalil]

Dear Experts;

My data is consist around 100 row, most them highlited with different colors;

I need to do the following; Please help me

1.. Count by Color
2.. Sum by color
3.. Filter by color

thanks in advance
khalil

 

[Ron de Bruin]

Excel version ?

 

[Gord Dibben]

See Chip Pearson's site for working with the colorindex of cells.

http://www.cpearson.com/excel/colors.aspx


Gord Dibben MS Excel MVP

 

[Shane Devenshire]

Hi,

You can do Filter by Color in Excel 2007.

For count and sum by color you will need a custom VBA function, and the
function will depend on the version of Excel you are using.


Here is a custom function to count cells base on font color and fill color:

Function CountFormats(R As Range, E As Range) As Integer
Dim cell As Range
Dim Total As Integer
Application.Volatile
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
With cell
If .Interior.ColorIndex = S.Interior.ColorIndex _
And .Font.ColorIndex = S.Font.ColorIndex Then
Total = Total + 1
End If
End With
Next cell
CountFormats = Total
End Function

In the spreadsheet you enter =countformats(A2:B7,D2) Where A2:B7 is the
range you want to check and D2 is a cell formatted to the desired format.

To add this code to a workbook press Alt+F11 and select your file in the
Project explorer in the top left side of the screen. Choose Insert, Module.
Put the code in the resulting module.