Excel 2003 / 2007 compatibility - colour sum

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

millo87

Thread Starter
Joined
Sep 23, 2008
Messages
1
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 -


Code:
 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
 
Joined
Jul 1, 2005
Messages
8,546
Welcome to board.

I'm guessing "check macro security settings" at the other board didn't help.

There are various flavours of UDF for this; tried this one?

Function SumByColor(InputRange As Range, ColorRange As Range) As Double
' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor($A$1:$A$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum
Dim cl As Range, TempSum As Double, ColorIndex As Integer
' Application.Volatile ' this is optional
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempSum = 0
On Error Resume Next ' ignore cells without values
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempSum = TempSum + cl.Value
End If
Next cl
On Error GoTo 0
Set cl = Nothing
SumByColor = TempSum
End Function
 
Joined
Sep 24, 2007
Messages
869
Millo where the code is saved ?
In the active workbook (the one where you wanna use formula) or in Personal.xls?

Try to insert the formula through Insert Formula Dialog box, select User defined category, and you should find there you formula (with the Personal.xls before the name if it's saved in Personal.xls)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top