1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2003 / 2007 compatibility - colour sum

Discussion in 'Business Applications' started by millo87, Sep 23, 2008.

Thread Status:
Not open for further replies.
  1. millo87

    millo87 Thread Starter

    Sep 23, 2008

    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
            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?

  2. bomb #21

    bomb #21

    Jul 1, 2005
    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
  3. Aj_old


    Sep 24, 2007
    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)
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/752553

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice