On 2002-11-06 06:43, ant wrote:
(I'm not sure if this posted, so I'll try again)
Is it possible to create conditional statements based on the color of a cell:
I would like to sum a column but omit the cells that I decide to put in yellow (or any chosen color).
Thanks!
ant.
Hi ant,
Try this UDF code, I found it in:
http://www.cpearson.com/excel/colors.htm
and modified it to your need.<font size=-1> Function SumMinusColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a minus background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If Not OK And IsNumeric(Rng.Value) Then
SumMinusColor = SumMinusColor + Rng.Value
End If
Next Rng
End Function</font size>
The ColorIndex for Yellow is 6.
Regards,
Eli
This message was edited by eliW on 2002-11-06 17:05