Adding numbers with the same background color

[gsgriffin]

Here's a question that I could REALLY use help with...

I have a matrix with about 20 columns and 500 rows. The values inside the matrix has been given different background colors to provide a visual indicator as to what type of value they are. I have Red, Yellow, and Green cells.

Problem: I must be able to total each column and break out the sum value for the red cells, yellow cells, and green cells.

How can I do this?

Example:

12 5 4
6 8 7
2 3 7
4 8 4

The answers I need are:
Column 1: Red=12 Yellow=2 Green=10
Column 2: Red=0 Yellow=8 Green=16
Column 3: Red=11 Yellow=11 Green=0

 

[pvw]

Hi gsgriffin

I hope you're using Excel. I wrote a little function which does your task:

Public Function SumBGColor(CellRange As Range) As String
Dim rngCell As Range
Dim dblSumRed As Double
Dim dblSumYellow As Double
Dim dblSumGreen As Double
Dim dblSumOther As Double
For Each rngCell In CellRange
If IsNumeric(rngCell) Then
If rngCell.Interior.ColorIndex = 3 Then
dblSumRed = dblSumRed + rngCell.Value
ElseIf rngCell.Interior.ColorIndex = 6 Then
dblSumYellow = dblSumYellow + rngCell.Value
ElseIf rngCell.Interior.ColorIndex = 10 Then
dblSumGreen = dblSumGreen + rngCell.Value
Else
dblSumOther = dblSumOther + rngCell.Value
End If
End If
Next
SumBGColor = "Red=" & dblSumRed & ", Yellow=" & dblSumYellow & ", Green=" & dblSumGreen & ", Other=" & dblSumOther
End Function

Put the code in a standard vba module. I used ColorIndex 3/6/10 for red/yellow/green. I added 'Other' to be sure that all cells have correct colors.

Just call the function in any cell and pass the column/range as argument. For example
=SumBGColor(A1:A500)
for 500 rows of column 'A'. Don't specify the entire column "A:A" as argument (it works but freezes Excel for many seconds). For each column call SumBGColor with the cell range you want to summarize.

Note that the formulas are not automatically recalculated after you change the background color of a cell, so the results are not correct. You have to press CTRL+ALT+F9 to force the calculation.

Hope this helps
Philipp

 

[gsgriffin]

THIS IS PERFECT. WITH ONLY A COUPLE EASY ADDS, THIS WILL DO EXACTLY WHAT I NEED!!!!!!!

AWESOME!!!!! THANKS SOOOOO MUCH!!!!!

G. Griffin