Conditional Statements With Different Color cells

[ant]

(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.

 

[zzdave]

Yeah I'm sure it is but you may have to write your own function in VB to do it.

 

[eliW]

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