Condition Function

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Is it possible to calculate the sum of a row excluding
cell that are colour hi-lighted?

If you know how to do this please let me know, thanks
 
One way:

Public Function SumNoColor(ByRef rng As Range) As Variant
Dim rCell As Range
Application.Volatile True
For Each rCell In rng
With rCell
If .Interior.ColorIndex = xlColorIndexNone Then _
If IsNumeric(.Value) Then _
SumNoColor = SumNoColor + .Value
End With
Next rCell
End Function


Note that this will not automatically update when a background color is
changed. The Application.Volatile statement will recalculate the
function whenever the worksheet is recalculated (including when you type
F9).

If you're unfamiliar with UDFs, see David McRitchie's "Getting Started
with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top