Cell not updating

[Lynton]

I have a spreadsheet containing numerous Formulas that all work perfectly.
Basically its an invoice system that records invoices as they are created.
The invoice record sheet creates the invoices in cells with a yellow background colour.
When the invoice is paid the cell background colour is changed to "no fil".
I want to sum the total amounts in one column of only the Yellow cells.

I have created a Function:
--------------------------------------------------------------------
Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.Colorindex
For Each cl In rRange
If cl.Interior.Colorindex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function
___________________________________________________________________
On the worksheet I have entered =SumByColor(D2,E6:E2000)
Where D2 is always yellow and E6:E2000 are the cells I need "summed" if they are yellow.
This all works well.
But it doesn't update if I add another invoice to the list ?
If I delete the cell containing SumByColor(D2,E6:E2000) and then press ctrl z (undo) it calculates correctly.
all other formulas work correctly and update as required.
any help appreciated.

 

[Colo]

Try adding the following code to the bigging line of your UDF.
Just under Function SumByColor(CellColor As Range, rRange As Range) is OK.

application.volatile

This method marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet.

 

[jolivanes]

How are the cells colored? Conditional Formatting?
If so, you need to use "DisplayFormat.Interior.Color"

 

[Lynton]

Try adding the following code to the bigging line of your UDF.
Just under Function SumByColor(CellColor As Range, rRange As Range) is OK.

application.volatile

This method marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet.

Many thanks iv done that but it still doesn't update.