Total of shaded cells

[Montoya]

I am trying to show the total of a number of shaded cells (all one colour) in a worksheet, scattered over a number of rows/columns. There is no conditional formatting, someone has highlighted the cells before sending to me. Please help!!!

 

[Steve Hartman]

see http://www.mrexcel.com/board/viewtopic.php?topic=1540&forum=2&2

 

[NateO]

You could search and find something like the following, but I wanted to add "application.volatile" to it to allow it to re-calc properly.

Paste this custom function in a "normal" vba module:

Function CountByColor(InputRange As Range, ColorRange As Range) As Double
Dim cl As Range, TempCount As Double, ColorIndex As Integer
Application.Volatile
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
On Error Resume Next
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then TempCount = TempCount + 1
Next cl
On Error GoTo 0
Set cl = Nothing
CountByColor = TempCount
End Function



Now use the following formula:

=CountByColor(A3:A9,A1)

Where the first paramter is the range to count and the second paramter (cell a1) has the color criteria to evaluate.


Hope this helps. Cheers,

Nate
This message was edited by NateO on 2002-03-11 19:08