Sum Dynamic Ranges Using Cells Formatted Fill Color

[norts55]

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.

 

[Eric W]

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

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]

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]

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!