Excel question

[munckee]

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?

 

[clayj]

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)

 

[munckee]

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.

 

[munckee]

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.

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)

 

[clayj]

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.

 

[munckee]

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?

 

[clayj]

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.