SumColour Macro works in 2003 but not 2007

[hvaleagues]

I have a macro that allows me to sum cells based on the shading of the cell. This works wonderfully in 2003, but when I send the file to someone using 2007 (ie my boss), the macro doesn't work and produces a ton of #NAME? errors, instead of giving him the information he needs. Here is the code:

Function SumColoured(oRange As Range, lColorIndex As Variant) As Variant
    Dim oCell As Range
    Dim dSum As Double
    Application.Volatile
    For Each oCell In oRange
        If oCell.Interior.ColorIndex = lColorIndex Then
            dSum = dSum + Val(oCell.Value)
        End If
    Next
    SumColoured = dSum
End Function

Is there something I can change in here to make it work on 2007 (and 2010).

Any help would be greatly appreciated.

 

[P.Holko]

It seems that Your boss must change macro security settings in Excel to "Enable all macros"
regards, PH

 

[shg]

It seems that Your boss must change macro security settings in Excel to "Enable all macros"

You really don't want your boss to do that.

Excel Options > Trust Center > Trust Center Settings > Macro Settings, tick Disable all macros with notification.

Then teach her to look for the security warning below the ribbon, and enable macros only when the workbook comes from someone she trusts, like you.

 

[hvaleagues]

Thanks. I was initially told that he was enabling macros, but upon further questioning he wasn't. I had him change his settings per shg's suggestions.

Thanks again!