# Sum Dynamic Ranges Using Cells Formatted Fill Color

#### norts55

##### Board Regular
I have a routine/macro that populates cost of multiple items into column H of a worksheet (Sheet1). These costs are in groups. These groups can have 1 row, 101 rows or whatever, the numbers of rows change depending on how many items I need in each grouping. The number of groupings also change. After that macro runs, I need to sum the groupings. Right now, I have to pick each individual cell where I want the summed totals and then hit the AutoSum function. This can be very tedious, and I can miss some of the locations.

Does anyone know how to have a macro sum the cost of these groups in Col H after they are populated?

The range(s) of cells needing to be summed will already be formatted with a fill color of gray (See image below). The cell(s) that will have the summed total will already be formatted with the fill color orange (See image below). The hope is that a macro can find these formatted cells and create the range(s) needed and insert the sum function where needed.

I have scoured the internet and multiple forums to find a macro similar to my needs, as I know there are a lot of macros out there summing dynamic ranges, but I cannot find anything that resembles is what I need, and this is way over my knowledge of programing (which is very little).

Any help would be greatly appreciated.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Eric W

##### MrExcel MVP
It would make more sense to put this logic in your existing macro. But if that's not feasible, try this macro:

VBA Code:
``````Sub TotalIt()
Dim r As Long

Range("H1").Select
ActiveCell.End(xlDown).Select
While ActiveCell.Row < Rows.Count
r = ActiveCell.Row
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1).Formula = "=SUM(H" & r & ":H" & ActiveCell.Row & ")"
ActiveCell.Offset(1).End(xlDown).Select
Wend
Range("H1").Select

End Sub``````

This macro doesn't look for colors at all. It just looks for groups of numbers and puts a SUM at the end of each group. See if it works for you.

#### norts55

##### Board Regular
That works great. THANK YOU! I have been struggling trying to figure out the range selection. I didn't know your could find groups like this.

#### Eric W

##### MrExcel MVP
Yes, the .End(xlDown) (as well as the xlUp, XlToRight, and XlToLeft) methods are the equivalent of pressing Control-(down arrow), and they will find the start/end of the next/current section. Quite handy!

Replies
5
Views
266
Replies
3
Views
68
Replies
5
Views
55
Replies
5
Views
87
Replies
6
Views
214

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,564
Messages
5,770,886
Members
425,649
Latest member
cbTexas

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back