REDS WORTH ZERO
Posted by Keighley Peters on June 27, 2001 1:18 AM
I have a spreadsheet that contains values in black and also red. I would like the formula to read the red values as zero when calculating totals. Please help
Posted by Dax on June 27, 2001 3:21 AM
Hello,
I don't think there's a way of doing this with any of Excel's built in capabilities so a custom function may be needed. If you open the Visual Basic editor (alt+F11), click Insert, Module and paste this code. You can then go onto your worksheet and type =ConditionalColourSum(A1:A10) and you'll be given the magic number.
Public Function ConditionalColourSum(rnge As Range) As Double
Application.Volatile
Dim Total As Double, cl As Range
'Exclude cells with red font from sum
For Each cl In rnge.Cells
If cl.Font.Color <> vbRed Then
Total = Total + cl.Value
End If
Next
ConditionalColourSum = Total
End Function
HTH,
Dax.
