Total of shaded cells

Montoya · Mar 7th, 2002, 10:16 AM

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 · Mar 7th, 2002, 10:55 AM

see http://www.mrexcel.com/board/viewtop...1540&forum=2&2

NateO · Mar 7th, 2002, 11:46 AM

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 ]

Mar 7th, 2002, 10:55 AM	#2
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	see http://www.mrexcel.com/board/viewtop...1540&forum=2&2 __________________ Veni, Vidi, Velcro - I came, I saw, I stuck around Taxation WITH representation ain't so hot either

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 10:16 AM	#1
Montoya New Member Join Date: Mar 2002 Posts: 1	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!!!

Mar 7th, 2002, 11:46 AM	#3
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 ]