Can I make if functions based on fill color?

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?

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>