Excel 2003 / 2007 compatibility - colour sum

[JayDM]

Hi

I'm supposed to be calculating the sum of cells based on their font colour.

i'm working in excel 2003, and i've got the Vba module code for the process -

 Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
     
    
    lCol = rColor.Font.ColorIndex
     
    If SUM = True Then
        For Each rCell In rRange
            If rCell.Font.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell
    Else
        For Each rCell In rRange
            If rCell.Font.ColorIndex = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If
     
    ColorFunction = vResult
End Function

i've got it to work, and add up all of the sums of the colours - no problem.

I then saved it, and went to my colleagues computer, running office (excel) 2007, tried to open the file - it opens, but where i've wrote the cell formula (sample: =ColorFunction($G5060,CU$761:CU$5053,TRUE) )all i get is #NAME? in every cell, i've tried rewriting the formula, rewriting the vb code (copy paste), but i still cant get it to show the formula results.

just to confirm - it works in 2003, but not 2007.

what am i doing wrong?

Thanks

 

[combo]

Are the macros enabled?

combo

 

[AnotherHiggins]

Following up Combo's post - check macro security settings.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.