Colored Cells & Cell Formulas

[MoltenPoo]

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?

 

[Nimrod]

What does the difference in color represent ?

 

[MoltenPoo]

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.

 

[Nimrod]

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.

 

[MoltenPoo]

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

 

[Joe Was]

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

 

[MoltenPoo]

THANKS!

 

[Nimrod]

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.

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

 

[Nimrod]

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:

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

 

[Lo Lun To]

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.