Solved

Excel - Total by Color (Not Using UDF)

Posted on 2014-12-23
4
160 Views
Last Modified: 2014-12-24
Hello Experts,

I would like to modify the code I came across, which sums all cells that have the same background color, please see attached file, with code.  I am using Excel 2010.  There are two limitations that this code has, which I've been trying to overcome for the past three weeks, but I cannot get it to work.

1. Color Index

By recording a macro in Excel 2010, where I colored cells at random, I noticed that the macro code made reference to the Selection.Interior.Pattern, *.PatternColorIndex, *.ThemeColor, *.TintAndShade, *.PatternTintAndShade instead of just the *.Interior.ColorIndex that the code I have attached in module 1 uses.  In a nutshell, I would like to match the cell background color of my target cell, see D25 in attached file and insert all the matching cell address "=+B13+B14...."

2. Range Of Cells

The second limitation that this code has is that it only applies to cells directly above the target cell, in the attached file, this cell is F25.  Ideally, the macro should prompt user to select range in which to look for the matching cells.
The cells will not be colored using conditional formatting, they'll be directly formatted using the fill color function.

The purpose of all this is for audit-trail purposes, and hence the need to list all cell addresses in one long formula.

Thank you very much for any help you can provide.
Sample-Data---Copy---Copy.xlsm
0
Comment
Question by:SuraDalbin
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40516017
First, select the range you want to sum, and run this.
Sub AddColour()
    Dim iterCell As Range
    Dim SRow As Integer
    Dim SCol As Integer
    Application.Calculation = xlCalculationManual
    
    SRow = InputBox("Enter sum cell row")
    SCol = InputBox("Enter sum cell col")
    
    Cells(SRow, SCol).Formula = "="
    For Each iterCell In Selection
        If iterCell.Interior.Color = Cells(SRow, SCol).Interior.Color Then
            Cells(SRow, SCol).Formula = Cells(SRow, SCol).Formula & "+" & iterCell.Address
        End If
    Next iterCell
    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

0
 

Author Comment

by:SuraDalbin
ID: 40516048
Hello Hakan,

Thank you very much for your response.  I tested the code, but on the first couple of attempts, it seems that the code enters the correct resulting formula in the (row, col) address that I entered in the inputboxes, overwriting the original value in that cell.  This cell however, was supposed to be included in that resulting formula.
To make it a bit more intuitive, can the code prompt the User for two range entries, one would be to select the range to be summed and the second entry would be to ask the User to select the cell that contains the color to be summed?

Thanks again,

Sura
0
 
LVL 5

Accepted Solution

by:
Hakan Yılmaz earned 500 total points
ID: 40516065
You can use this to write formula to multiple cells based on their colors.

e.g. make a cell green and make a cell red; select them as result cells. It will write sum formulas for both.

Sub AddColour()
    Dim resrange As Range
    Dim iterresrange As Range
    
    Dim sumrange As Range
    Dim itersumrange As Range

    Application.Calculation = xlCalculationManual
    
    Set sumrange = Application.InputBox("select cells you want to include in sums", , , , , , , 8)
    Set resrange = Application.InputBox("select cells you want to write formulas to", , , , , , , 8)
    
    For Each iterresrange In resrange
        iterresrange.Formula = "="
        For Each itersumrange In sumrange
            If itersumrange.Interior.Color = iterresrange.Interior.Color Then
                iterresrange.Formula = iterresrange.Formula & "+" & itersumrange.Address
            End If
        Next itersumrange
    Next iterresrange
    
    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window


RESULT
0
 

Author Closing Comment

by:SuraDalbin
ID: 40516114
Hakan,

Thank you so much, this works perfectly.  You rock!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
first name and last initial in excel 11 29
Text box keydown event does not work 38 42
A Table within a User Form 24 48
Index Match Formula VBA 6 21
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question