Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Colored Cells & Cell Formulas

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    New Jersey
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there any way to utlized cell formulas to determine cell or text color? For example, if I have data populating A1:A5 and A1:A2 have a fill color of yellow and A3:A5 have a fill color of blue, can I use some formula with a "SUMIF" command to add by color?

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What does the difference in color represent ?

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    New Jersey
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In this case, the colors represetn different categories of taxes. I realize that I can set up a column of data next to the column with colors and create some unique naming convention next to each color (category) but the list is huge - 35k+ rows so being able to use the colors themselves as the differentiator would be awesome.

    Any thoughts? I do not want to use code here if at all possible, that's why I asked for a cell formula.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry I've can't find anything that will allow you to do a sumif by color. I could write you a small program that would add a new column with a variable for each color.
    Then you could base you sumif criteria of this new row.

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    New Jersey
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for looking. While I really do not want to use code I suppose it wouldn't hurt to take a look. What would the small proggie look like - If it's not a problem?

    [ This Message was edited by: MoltenPoo on 2002-05-10 12:05 ]

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a macro to find colored cells. It may get you started. You can add code to copy data from a range to another location or operate on each found item. JSW

    Sub myColor()
    'By Joe Was
    Dim myRowNum As Long
    myRowNum = ActiveSheet.UsedRange.Rows.Count
    Selection.Select
    Do Until Selection.Row = myRowNum + 1
    'Look for any cell row with a background color.
    If Selection.Interior.ColorIndex <> xlNone Then
    'To select entire row, un-comment below!
    'Selection.EntireRow.Select
    GoTo mySelect
    Else
    Selection.Offset(1, 0).Select
    End If
    Loop
    End
    mySelect:

    End Sub

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    New Jersey
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    THANKS!

  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So I've written you a little Function called "ColorSumIf" . Put this function in any cell in your spreadsheet and it will sum all the numbers in a specific color of cell. With the formula you indicate a cell in the row you want to total ( that is the correct color) and it will return the sum.

    Example : lets say you want to total all the cells in Col C that were red and C4 is a red cell.
    Then just type in the following function into any cell.. other then one in row C.

    = ColorSumIf(4,3)
    ( ie: ColorSumIf(RowRef,ColRef))

    Before attempting this you must insert the following code into your workbook.
    Code:
    Public Function ColorSumIf(Rw, Cm)
    Do
    rwIndex = rwIndex + 1
            With Cells(rwIndex, Cm)
                If .Interior.ColorIndex = Cells(Rw, Cm).Interior.ColorIndex Then
                    ColorSumIf = ColorSumIf + .Value
                End If
            End With
    Loop Until Len(Trim(Cells(rwIndex, Cm).Value)) = 0
    End Function
    To insert code
    1- right click on worksheet name tab
    2- select view code
    3- editor window will open
    4- click on "insert" on editor toolbar
    5- select insert module
    6- paste in the code from here

    [ This Message was edited by: Nimrod on 2002-05-10 15:18 ]

    [ This Message was edited by: Nimrod on 2002-05-10 15:20 ]

    [ This Message was edited by: Nimrod on 2002-05-10 15:26 ]

  9. #9
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ColorSumIf...Version 2

    In this version the Function takes the color from the cell its in. The only parameter you give to it is the number of the column you want evaluated:

    For example if you want to have your answer in D2 for the Column C Red Cells then you enter the following Function in D2:
    =ColorSumIf(3)
    AND make the color of D2 RED.

    Here is the code for this version:
    Code:
    Public Function ColorSumIf(Cm)
    Do
    rwIndex = rwIndex + 1
            With Cells(rwIndex, Cm)
                If .Interior.ColorIndex = ActiveCell.Interior.ColorIndex Then
                    ColorSumIf = ColorSumIf + .Value
                End If
            End With
    Loop Until Len(Trim(Cells(rwIndex, Cm).Value)) = 0
    
    End Function

  10. #10

    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think a VBA solution would the best, but here's a non-VBA way.

    - Define a name (lets say Clr) and type in the RefersTo box =GET.CELL(38,INDIRECT("rc[-1]",FALSE))

    - Insert a new column immediately after column A.

    - In the new column put in cells B1:B5 =Clr

    Cells B1:B5 will then show the ref numbers for the fill-colours used for A1:A5

    You can then use the numbers in B1:B5 for your SUMIF formula.

    Column B could be kept hidden.

    Note : The formulas in column B will only update on a sheet recalculation.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •