Can I autosum cells between colored rows?

[Abikin]

Hello, I am new to this forum, and have looked around but have not seen a post quite like this.
I am temping at an office that is preparing a huge budget planning spreadsheet. My client extracted data from Access, prepared a pivot table, highlighted subtotals at various levels with different colors, and tidied up.
Next, to allow the spreadsheet to be used without relying on the underlying data, he saved to a new spreadsheet by pasting as values.
In order to plan the budget for the coming year, the shaded rows with subtotals need to have active subtotal formulas. Managers will enter their requests for each line item, and they need to see totals as they go.
There are nested subtotals at four levels, each level a different color. So each area has a whole bunch of orange subtotals that roll up to yellow subtotals, those total to the blues ones, then to the final black rows.
I've spent two days manually pressing the autosum button! which is OK, but for the first orange subtotal of a group, when I autosum it selects up through the yellow row above, and I have to drag the range down a row. Once I do a column, I use a macro to copy the formula cells to additional columns.
Is there a macro that would allow me to sit on an orange cell and total up all cells beneath the next cell of any color? That would be handy for the orange first level sums. One that would stop just below the next cell of the same color would be good for the upper level sums.
I have a basic grasp of macros, but was just laid off from a job where SQL was used, so I have some aptitude and am catching on....
Thanks in advance for any help!
AK
PS- the next thing I have to do is split this up into separate sheets for each dept, any pointers in advance would also be appreciated. I don't know why he didn't do separate department reports from Access?

 

[VoG]

This may be a start http://www.ozgrid.com/VBA/Sum.htm

 

[Abikin]

Hi Peter - This could be a starting point, perhaps for someone with more skills than I have.....
It looks like most of what I am finding on various boards will select cells based on color. I want to use color a little differently.
What I would hope to do would be to build a range, starting one cell from the cursor, and sequentially look at each cell above.
- If the cell does not match the color of the cell that the cursor is on, then add that cell to the range and go on to look at the next cell.
- If the cell does match the color of the cell that the cursor is on, then do not add that cell to the range, and stop adding cells to the range.
- After the range has been set by that process, then autosum the cells in the range.
Maybe this is a better explanation?
Thanks for any help!
Abikin
P.S. - I did find a solution to my worksheet splitting task, in one of your earlier posts to somebody else - thanks!!

 

[VoG]

If your worksheet looks like this

<b>Sheet1 (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">X</td><td style="background-color:#ffff00; text-align:right; ">53</td><td style="background-color:#ffff00; text-align:right; ">71</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">N</td><td style="background-color:#ffff00; text-align:right; ">43</td><td style="background-color:#ffff00; text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; ">X</td><td style="background-color:#ffff00; text-align:right; ">1</td><td style="background-color:#ffff00; text-align:right; ">15</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff00; ">N</td><td style="background-color:#ffff00; text-align:right; ">49</td><td style="background-color:#ffff00; text-align:right; ">93</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; "> </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffcc00; ">L</td><td style="background-color:#ffcc00; text-align:right; ">12</td><td style="background-color:#ffcc00; text-align:right; ">68</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffcc00; ">O</td><td style="background-color:#ffcc00; text-align:right; ">42</td><td style="background-color:#ffcc00; text-align:right; ">20</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffcc00; ">O</td><td style="background-color:#ffcc00; text-align:right; ">12</td><td style="background-color:#ffcc00; text-align:right; ">85</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffcc00; ">D</td><td style="background-color:#ffcc00; text-align:right; ">59</td><td style="background-color:#ffcc00; text-align:right; ">82</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffcc00; ">W</td><td style="background-color:#ffcc00; text-align:right; ">55</td><td style="background-color:#ffcc00; text-align:right; ">33</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffcc00; ">H</td><td style="background-color:#ffcc00; text-align:right; ">24</td><td style="background-color:#ffcc00; text-align:right; ">50</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffcc00; ">J</td><td style="background-color:#ffcc00; text-align:right; ">76</td><td style="background-color:#ffcc00; text-align:right; ">72</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffcc00; ">W</td><td style="background-color:#ffcc00; text-align:right; ">3</td><td style="background-color:#ffcc00; text-align:right; ">22</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffcc00; ">D</td><td style="background-color:#ffcc00; text-align:right; ">87</td><td style="background-color:#ffcc00; text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#ffcc00; ">I</td><td style="background-color:#ffcc00; text-align:right; ">8</td><td style="background-color:#ffcc00; text-align:right; ">37</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >16</td><td style="background-color:#ffcc00; ">B</td><td style="background-color:#ffcc00; text-align:right; ">94</td><td style="background-color:#ffcc00; text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >17</td><td style="background-color:#ffcc00; "> </td><td style="background-color:#ffcc00; "> </td><td style="background-color:#ffcc00; "> </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#ff0000; ">B</td><td style="background-color:#ff0000; text-align:right; ">15</td><td style="background-color:#ff0000; text-align:right; ">46</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >19</td><td style="background-color:#ff0000; ">E</td><td style="background-color:#ff0000; text-align:right; ">80</td><td style="background-color:#ff0000; text-align:right; ">68</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#ff0000; ">V</td><td style="background-color:#ff0000; text-align:right; ">86</td><td style="background-color:#ff0000; text-align:right; ">98</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >21</td><td style="background-color:#ff0000; ">M</td><td style="background-color:#ff0000; text-align:right; ">13</td><td style="background-color:#ff0000; text-align:right; ">44</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >22</td><td style="background-color:#ff0000; ">S</td><td style="background-color:#ff0000; text-align:right; ">12</td><td style="background-color:#ff0000; text-align:right; ">76</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >23</td><td style="background-color:#ff0000; "> </td><td style="background-color:#ff0000; "> </td><td style="background-color:#ff0000; "> </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Then a macro like this should help

Sub sbttls()
Dim aArea As Range
For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
    Cells(aArea.Row + aArea.Rows.Count, 2).Value = WorksheetFunction.Sum(Range(Cells(aArea.Row, 2), Cells(aArea.Row + aArea.Rows.Count - 1, 2)))
    Cells(aArea.Row + aArea.Rows.Count, 3).Value = WorksheetFunction.Sum(Range(Cells(aArea.Row, 3), Cells(aArea.Row + aArea.Rows.Count - 1, 3)))
Next aArea
End Sub

Result

<b>Sheet1 (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">X</td><td style="background-color:#ffff00; text-align:right; ">53</td><td style="background-color:#ffff00; text-align:right; ">71</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">N</td><td style="background-color:#ffff00; text-align:right; ">43</td><td style="background-color:#ffff00; text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; ">X</td><td style="background-color:#ffff00; text-align:right; ">1</td><td style="background-color:#ffff00; text-align:right; ">15</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff00; ">N</td><td style="background-color:#ffff00; text-align:right; ">49</td><td style="background-color:#ffff00; text-align:right; ">93</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; text-align:right; ">146</td><td style="background-color:#ffff00; text-align:right; ">180</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffcc00; ">L</td><td style="background-color:#ffcc00; text-align:right; ">12</td><td style="background-color:#ffcc00; text-align:right; ">68</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffcc00; ">O</td><td style="background-color:#ffcc00; text-align:right; ">42</td><td style="background-color:#ffcc00; text-align:right; ">20</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffcc00; ">O</td><td style="background-color:#ffcc00; text-align:right; ">12</td><td style="background-color:#ffcc00; text-align:right; ">85</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffcc00; ">D</td><td style="background-color:#ffcc00; text-align:right; ">59</td><td style="background-color:#ffcc00; text-align:right; ">82</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffcc00; ">W</td><td style="background-color:#ffcc00; text-align:right; ">55</td><td style="background-color:#ffcc00; text-align:right; ">33</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffcc00; ">H</td><td style="background-color:#ffcc00; text-align:right; ">24</td><td style="background-color:#ffcc00; text-align:right; ">50</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffcc00; ">J</td><td style="background-color:#ffcc00; text-align:right; ">76</td><td style="background-color:#ffcc00; text-align:right; ">72</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffcc00; ">W</td><td style="background-color:#ffcc00; text-align:right; ">3</td><td style="background-color:#ffcc00; text-align:right; ">22</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffcc00; ">D</td><td style="background-color:#ffcc00; text-align:right; ">87</td><td style="background-color:#ffcc00; text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#ffcc00; ">I</td><td style="background-color:#ffcc00; text-align:right; ">8</td><td style="background-color:#ffcc00; text-align:right; ">37</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >16</td><td style="background-color:#ffcc00; ">B</td><td style="background-color:#ffcc00; text-align:right; ">94</td><td style="background-color:#ffcc00; text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >17</td><td style="background-color:#ffcc00; "> </td><td style="background-color:#ffcc00; text-align:right; ">472</td><td style="background-color:#ffcc00; text-align:right; ">486</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#ff0000; ">B</td><td style="background-color:#ff0000; text-align:right; ">15</td><td style="background-color:#ff0000; text-align:right; ">46</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >19</td><td style="background-color:#ff0000; ">E</td><td style="background-color:#ff0000; text-align:right; ">80</td><td style="background-color:#ff0000; text-align:right; ">68</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#ff0000; ">V</td><td style="background-color:#ff0000; text-align:right; ">86</td><td style="background-color:#ff0000; text-align:right; ">98</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >21</td><td style="background-color:#ff0000; ">M</td><td style="background-color:#ff0000; text-align:right; ">13</td><td style="background-color:#ff0000; text-align:right; ">44</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >22</td><td style="background-color:#ff0000; ">S</td><td style="background-color:#ff0000; text-align:right; ">12</td><td style="background-color:#ff0000; text-align:right; ">76</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >23</td><td style="background-color:#ff0000; "> </td><td style="background-color:#ff0000; text-align:right; ">206</td><td style="background-color:#ff0000; text-align:right; ">332</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Abikin]

Oh, this is definitely close!
On my table, though, *only* the subtotal rows (blank rows in your example) are colored. The rows that I want to add up are all unfilled.
If you can suggest a change to do that, much appreciated!
I'll also see if I can poke around on some sites to see if I can figure out what to change. I'm not getting how you are incorporating the color.
I'm definitely primed to start figuring this stuff out, I spent four hours today on this task!
Best Regards,
Abby

 

[alansidman]

Abby, you may want to put in a helper column that identifies the color index of each row and then do your sums based upon the color index. If that sounds like something you would be interested in, then look at this video on how to make a simple UDF that will give you the color index (which is a numerical value).

http://www.datapigtechnologies.com/flashfiles/SortonColor.html

Alan

 

[Abikin]

Whoa! that is great

But, now I also feel really foolish, because in setting up a test spreadsheet to work on this, I am finding that the autosum feature is doing exactly what I want it to do in this sheet, where it definintely does NOT in the spreadsheet I've been working on. Hmmm. I think I am having just an issue with the way this spreadsheet was created, or maybe it is my sequencing.

Lots of good threads to work with, not the last time I'll run into this. Thanks Peter and Alan for the pointers and the code. All great stepping stones!

Best regards, thanks again!
Abby

 

[VoG]

I think that you are getting misled by the coloring. The example I gave above is based not on color but on the data being divided into blocks. It will work just as well on an uncolored sheet, adding subtotals into the blank row below each chunk of data.

 

[Abikin]

OK, now I see! Yes, I was not thinking about it that way, but was focused on the color. This will definitely help - thanks!
Abikin