Count, Sum, and Filter by Color
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.
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Khalil" wrote:
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
|