SuraDalbin
asked on
Excel - Total by Color (Not Using UDF)
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.
, *.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...."
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
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.Pattern2. 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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hakan,
Thank you so much, this works perfectly. You rock!
Thank you so much, this works perfectly. You rock!
Open in new window