A friend has a worksheet that some cells are shaded, others aren't. The
shaded cells values are to be summed. Right now, he's writing a different
formula for each row of data to calculate the sum of the shaded cells. Is
there any way to test for a shaded cell and only put it's value into the
sum? Somehow to put that in say the first row of the sum column, and then
copy it down?


Thanks!
Shawn

--
Join the newest in Fantasy Sports.

http://www.6FantasySports.com
(this site supports the Victory Junction Gang Camp with a portion of all
proceeds)

Shawn, see if this will help

http://www.cpearson.com/excel/colors.htm
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"43fan" wrote in message
...
A friend has a worksheet that some cells are shaded, others aren't. The
shaded cells values are to be summed. Right now, he's writing a different
formula for each row of data to calculate the sum of the shaded cells. Is
there any way to test for a shaded cell and only put it's value into the
sum? Somehow to put that in say the first row of the sum column, and then
copy it down?


Thanks!
Shawn

--
Join the newest in Fantasy Sports.

http://www.6FantasySports.com
(this site supports the Victory Junction Gang Camp with a portion of all
proceeds)

Shawn,

It is not easy, but if you input the function supplied at the end, you can
do it with this formula

=SUMPRODUCT(--(ColorIndex(A1:A100)=3))

if you want the text colour, use

=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))

You could also set another cell, say C1, to the colour red, and test like
this

=SUMPRODUCT(--(ColorIndex(A1:A100)=ColorIndex(C1)))

So in your case, use the final version using a cell that has no shading.


Here's the function

'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
' Function: Returns the colorindex of the supplied range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

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

iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)

If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True, iBlack)
Else
aryColours = DecodeColorIndex(rng, False, iWhite)
End If

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

If text Then
aryColours(i, j) = DecodeColorIndex(cell, True, iBlack)
Else
aryColours(i, j) = DecodeColorIndex(cell, False, iWhite)
End If

Next cell

Next row

End If

ColorIndex = aryColours

End Function

Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, text As Boolean, idx As
Long)
Dim iColor As Long
If text Then
iColor = rng.font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"43fan" wrote in message
...
A friend has a worksheet that some cells are shaded, others aren't. The
shaded cells values are to be summed. Right now, he's writing a different
formula for each row of data to calculate the sum of the shaded cells. Is
there any way to test for a shaded cell and only put it's value into the
sum? Somehow to put that in say the first row of the sum column, and then
copy it down?


Thanks!
Shawn

--
Join the newest in Fantasy Sports.

http://www.6FantasySports.com
(this site supports the Victory Junction Gang Camp with a portion of all
proceeds)