View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Chip Pearson Chip Pearson is offline
external usenet poster
 
Posts: 7,247
Default subtotal colored cells

You could try the following code. The first function, CellsAreVisible
returns an array of 1s and 0s indicating whether the cell is visible. The
second returns an array of 1s and 0s indicating whether the cell has a
specific colorindex (either of font or background).


Function CellsAreVisible(InRange As Range, TestRows As Boolean) As Variant

Dim Arr() As Long
Dim R As Long
Dim C As Long

ReDim Arr(1 To InRange.Rows.Count, 1 To InRange.Columns.Count)

For R = 1 To InRange.Rows.Count
For C = 1 To InRange.Columns.Count
If TestRows = True Then
If InRange(R, C).EntireRow.Hidden = True Then
Arr(R, C) = 0
Else
Arr(R, C) = 1
End If
Else
If InRange(R, C).EntireColumn.Hidden = True Then
Arr(R, C) = 0
Else
Arr(R, C) = 1
End If
End If
Next C
Next R

CellsAreVisible = Arr

End Function



Function CellHasColor(InRange As Range, ColorIndex As Long, OfText As
Boolean) As Variant

Dim Arr() As Long
Dim R As Long
Dim C As Long

ReDim Arr(1 To InRange.Rows.Count, 1 To InRange.Columns.Count)
For R = 1 To InRange.Rows.Count
For C = 1 To InRange.Columns.Count
If OfText = True Then
If InRange(R, C).Font.ColorIndex = ColorIndex Then
Arr(R, C) = 1
Else
Arr(R, C) = 0
End If
Else
If InRange(R, C).Interior.ColorIndex = ColorIndex Then
Arr(R, C) = 1
Else
Arr(R, C) = 0
End If
End If
Next C
Next R

CellHasColor = Arr

End Function

You can then multiply the arrays together and with a range of number to get
the sum of the numbers in cells that are visible and have given font color.
For example, the following formula will SUM the cells in A1:A10 that are
visible and have a background color of red.

=SUMPRODUCT(CellAreVisible(A1:A10,TRUE),CellHasCol or(A1:A10,3,FALSE),A1:A10)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"april" wrote in message
...
i used the pearson macro to add specifically colored cells. however, i
have
a large spreadsheet and must add multiple ranges so i have to go to each
range and adjust the range that i want to add. the subtotal function is
perfect for setting the range, but it adds everything in the range and i
only
want to add cells with a certain fill color in that range. tried this
SumByColor(SubTotal(9,H19:H22),35,false)) but got a VALUE error. Any
suggestions?

Thanks


--
aprilshowers