Excel question

Discussion in 'Mac Apps and Mac App Store' started by munckee, Oct 31, 2006.

  1. munckee macrumors 65816

    Joined:
    Oct 27, 2005
    #1
    Can anyone help me out on this? Is it possible, in excel, to have the program recognize a fill color?

    For example, I have a spreadsheet that's tracking numbers. I'd like to have the program recognize which numbers are in a blue box and which are in an orange box, then have it add the numbers in each color. Is that possible?
     
  2. clayj macrumors 604

    clayj

    Joined:
    Jan 14, 2005
    Location:
    visiting from downstream
    #2
    This page shows how to do it:

    Summing The Values Of Cells With A Specific Color

    The following function will return the sum of cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False).

    Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Double
    '
    ' This function return the SUM of the values of cells in
    ' InRange with a background color, or if OfText is True a
    ' font color, equal to WhatColorIndex.
    '
    Dim Rng As Range
    Dim OK As Boolean

    Application.Volatile True
    For Each Rng In InRange.Cells
    If OfText = True Then
    OK = (Rng.Font.ColorIndex = WhatColorIndex)
    Else
    OK = (Rng.Interior.ColorIndex = WhatColorIndex)
    End If
    If OK And IsNumeric(Rng.Value) Then
    SumByColor = SumByColor + Rng.Value
    End If
    Next Rng

    End Function

    You can call this function from a worksheet cell with a formula like:

    =SUMBYCOLOR(A1:A10,3,FALSE)
     
  3. munckee thread starter macrumors 65816

    Joined:
    Oct 27, 2005
    #3
    Thanks Clay. I'm assuming I'll be back with questions later when I sit down to implement it, but i appreciate your pointing it out.
     
  4. munckee thread starter macrumors 65816

    Joined:
    Oct 27, 2005
    #4
    Ok, I'm far from an excel guru. Where do I enter all that, or what portion of it do I need to enter where? That's all a bit confusing.

     
  5. clayj macrumors 604

    clayj

    Joined:
    Jan 14, 2005
    Location:
    visiting from downstream
    #5
    The Function (through End Function) all needs to go into a VBA module in the workbook where you will be using the function... go to Tools > Macro > Visual Basic Editor to access a module and paste in the function text. (When you save the workbook, the module will be saved as part of it.)

    That's pretty much all you have to do. Don't forget that you will need to know what color equals what ColorIndex... a simple way to find that out is to write this macro:

    Sub WhatColor()
    MsgBox ActiveCell.Interior.ColorIndex
    End Sub

    Select a cell that contains a background color, and then run the macro. The message box that appears will tell you the number for the background color of the selected cell.
     
  6. munckee thread starter macrumors 65816

    Joined:
    Oct 27, 2005
    #6
    Ok, I think I got it working. One more problem though:

    The sum cell doesn't refresh itself when I fill more cells. I have to go in and change it manually. Is there some way to make it refresh itself? Or will it do it each time I open the doument?
     
  7. clayj macrumors 604

    clayj

    Joined:
    Jan 14, 2005
    Location:
    visiting from downstream
    #7
    I'm pretty sure it's only going to update when you force the cell to recalculate. This is a defensive measure designed to protect the user (you) from a worksheet filled with formulae that refer to complicated functions... if they recalced like normal formulae, you could end up sitting there for a VERY long time each time you made any sort of change.
     

Share This Page