Sum Dynamic Ranges Using Cells Formatted Fill Color

norts55

Board Regular
Joined
Jul 27, 2012
Messages
151
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.


1635519913406.png
1635519922157.png
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,865
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.
 
Solution

norts55

Board Regular
Joined
Jul 27, 2012
Messages
151
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
Joined
Aug 18, 2015
Messages
11,865
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!

Glad I could help! :cool:
 
Master Excel Bundle

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.

Forum statistics

Threads
1,152,569
Messages
5,770,907
Members
425,651
Latest member
Mark Cashin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top