Hello all and thanks for helping and reading my first post

I'm using Excel 2010.

I have a workbook with quite a few sheets on it so to simplify my problem I'll concentrate on just two worksheets, one called Ackroyds and the other Supplier Details.

What I'm doing is a simple invoice spreadsheet where on the Ackroyds sheet I input the invoice information and it lets me know when that invoice needs paying by highlighting it red. All working great.

On the supplier page I have a list of all the companies I deal with and one of the columns is Total Due Now. In this column I want it to look at the list of invoices for that supplier (in this example Ackroyds) and add up all the ones that are red so I can see the total amount due now (some companies give us 30 days to pay so wouldn't be included if still in grace period) to that company. Having battled with trying to loop through the cells in the column to see if they were red and discovering that reading the colour of conditional formatted cells is pretty much impossible, I have it checking the same formula used in the conditional formatting rules....again...all working great.

This is my function

Function checkIfDue(rng As Range)
    Dim tmpAmnt As Long
    Dim rowOffset As Long
    
    For Each rCell In rng.Cells
        If IsDate(rCell) Then
            If rCell < Date + 3 Then
                tmpAmnt = tmpAmnt + rng.Offset(rowOffset, 1).Value
            End If
        End If
        rowOffset = rowOffset + 1
    Next
    
    checkIfDue = tmpAmnt
End Function
and I call it using

=checkIfDue(Ackroyds!D2:D9)
So it loops through the range of dates to check if the invoice is due in the next 3 days. If it is I want it to add it to the variable tmpAmnt....this is where it errors. If I have the line
tmpAmnt = tmpAmnt + rng.Offset(rowOffset, 1).Value
set to
tmpAmnt = rng.Offset(rowOffset, 1).Value
then it correctly fills the cell with the last value of the range. It's only when I try to add them up I get the #VALUE! error.

Any takers to this problem?

Many thanks

Dave