summing shaded "filled" cells

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

I'm trying to sum or subtract only cells that are shaded or "filled" a
certain with a color. Is there a way to do this?
 
Marc,

There is a way, but it usually involves a helper column an d VBA to identify
the coloriundex of these cells.

Here is a slightlt different approach, that allows using SUMPRODUCT to
calculate them. Still needs VBA though.

Below is a custom function that will work out the colorindex of a range of
cells. To use it, enter it into a code module. The following worksheet call
will calculate how many items in A1:A100 have the same interior colour as B1

=SUMPRODUCT(--(colorindex(A1:A100)=colorindex(B1)))

or

if you want to test directly for the colorindex

=SUMPRODUCT(--(colorindex(A1:A100)=10))

This will calculaculate the sum of the values in A1:A100 that have the same
interior colour as B1

=SUMPRODUCT(--(colorindex(A1:A4)=colorindex(A1)),(A1:A4))

One major problem you should b e aware of is that if you change a colour in
the target range, the formula does not recalculate as the interior colour
doesn't trigger calculation, so you have to force it by editing the cell.

Here is the function.

Function ColorIndex(rng As Range) As Variant
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryColours As Variant

If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
aryColours = rng.Interior.ColorIndex

Else
aryColours = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

aryColours(i, j) = cell.Interior.ColorIndex
Next cell

Next row

End If

ColorIndex = aryColours

End Function
 
Back
Top