Hi, and thanks in advance for considering my question. Basically I need
to be able to sum a range of values in a very large spreadsheet based on
whether or not the cells have a fill color. I've tried looking up the
help files on if and sumif but they don't specify whether or not this
is possible... Anyone know? :)


--
nickclingan
------------------------------------------------------------------------
nickclingan's Profile: http://www.excelforum.com/member.php...o&userid=28623
View this thread: http://www.excelforum.com/showthread...hreadid=482862

You can but it is flawed as a colour change does not trigger worksheet
recalculation

Function GetColorindex(rng As Range)
GetColindex = rng.Interior.Colorindex
End Function

=If(GetColorindex(A2)=3,1,0)



--

HTH

RP
(remove nothere from the email address if mailing direct)


"nickclingan"
wrote in message
...

Hi, and thanks in advance for considering my question. Basically I need
to be able to sum a range of values in a very large spreadsheet based on
whether or not the cells have a fill color. I've tried looking up the
help files on if and sumif but they don't specify whether or not this
is possible... Anyone know? :)


--
nickclingan
------------------------------------------------------------------------
nickclingan's Profile:
http://www.excelforum.com/member.php...o&userid=28623
View this thread: http://www.excelforum.com/showthread...hreadid=482862

Maybe you can tweak this code to suit your needs. Sums the values of the
orange, red and green colored cells in a named range, "Data". Does not work
if cell color is due to conditional formatting. Dave McRitchie has a site
that deals with this very thing on the professional level, but I misplaced
the link, sorry.

Sub SumColorCount()
Dim Orange46 As Integer, _
Red3 As Integer, _
Green4 As Integer
Dim Cell As Range

For Each Cell In Range("Data")
If Cell.Interior.ColorIndex = 46 Then
Orange46 = Orange46 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 4 Then
Green4 = Green4 + Cell.Value
End If
Next

Range("F10").Value = "Orange = " & Orange46
Range("F11").Value = "Red = " & Red3
Range("F12").Value = "Green = " & Green4

MsgBox " You have: " & vbCr _
& vbCr & " Orange " & Orange46 _
& vbCr & " Red " & Red3 _
& vbCr & " Green " & Green4, _
vbOKOnly, "CountColor"

Range("F10").Value = ""
Range("F11").Value = ""
Range("F12").Value = ""
End Sub

Regards,
Howard

"nickclingan"
wrote in message
...

Hi, and thanks in advance for considering my question. Basically I need
to be able to sum a range of values in a very large spreadsheet based on
whether or not the cells have a fill color. I've tried looking up the
help files on if and sumif but they don't specify whether or not this
is possible... Anyone know? :)


--
nickclingan
------------------------------------------------------------------------
nickclingan's Profile:
http://www.excelforum.com/member.php...o&userid=28623
View this thread: http://www.excelforum.com/showthread...hreadid=482862