How to Calculate Coloured Cells in Excel 2003

1. Access Visual Basic Editor by clicking the 'Tools' button on the top of your screen, navigating to the 'Macro' option and then clicking on 'Visual Basic Editor.'
2. Copy and paste the following Visual Basic programming module into Visual Basic Editor, and then save it:Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)Dim rCell As RangeDim lCol As LongDim vResultlCol = rColor.Interior.ColorIndexIf SUM = True ThenFor Each rCell In rRangeIf rCell.Interior.ColorIndex = lCol ThenvResult = WorksheetFunction.SUM(rCell,vResult)End IfNext rCellElseFor Each rCell In rRangeIf rCell.Interior.ColorIndex = lCol ThenvResult = 1 vResultEnd IfNext rCellEnd IfColorFunction = vResultEnd Function
3. Utilize the user-created function ColorFunction to calculate the number of coloured cells. The syntax for this function is as follows:ColorFunction($A$1,$B$1:$B$2,FALSE)wherein $A$1 represents the cell you want to compare to, $B$1 and $B$2 represent a range of cells along a single column and TRUE tells the function to count.Given that you have a purple cell, B1, and you want to calculate the number of purple cells between rows C1 and C12, you would use the function as follows:ColorFunction(B1, C1:C12, FALSE)