Get Total of Specific Background Colored Cells

[johnbrownbaby]

Hello,

I am attempting to automatically get the sub-totals from each blocks (shown in P11-P13, AB11-AB13, P19-P21 and AB19-AB21) then get the overall total to show up in Column E of the three background color of the cells:

There are 4 blocks of data:
Block 1 runs between F9-O14
Block 2 runs between R9-AA14
Block 3 runs between F17-O22 and
Block 4 runs between R17-AA22

I was given a partial solution to get the total from a block via the code:

Option Explicit

Sub ColorTotal()
    Application.ScreenUpdating = False
    Dim c As Range
    Dim rng As Range
    Dim i As Long, x As Long
    Dim lr As Long, lr2 As Long, lc As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    lr2 = Range("F" & Rows.Count).End(xlUp).Row
    lc = Cells(9, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(9, 14), Cells(lr2, lc))
    For i = 6 To lr
        x = 0
        For Each c In rng
            If c.Interior.ColorIndex = Range("D" & i) Then
                x = x + 1
            End If
        Next c
        Range("E" & i) = x
    Next i
    Application.ScreenUpdating = True
    MsgBox "Complete!"
End Sub

Can you help me get the subtotals and the overall total of the different background colors in the spreadsheet?

 

[QuestionBaker]

Modify your if statement condition to following

If c.Interior.ColorIndex = Range("D" & i).Interior.ColorIndex Then

Enter data from B9:D9 in the same format and fill the cell with the colour that you want to find the total of.
Previous if statement was comparing the colour of the cell in the range with the ColorIndex mentioned in the the cell D6:D8.
The new if statement is modified to check for the colour of the cell instead of the value of the ColorIndex mentioned in the cell

 

[johnbrownbaby]

Modify your if statement condition to following

If c.Interior.ColorIndex = Range("D" & i).Interior.ColorIndex Then

Enter data from B9:D9 in the same format and fill the cell with the colour that you want to find the total of.
Previous if statement was comparing the colour of the cell in the range with the ColorIndex mentioned in the the cell D6:D8.
The new if statement is modified to check for the colour of the cell instead of the value of the ColorIndex mentioned in the cell

 

[johnbrownbaby]

Thanks for your input. I changed the code as per your suggestion. The total column is still showing up the wrong numbers. I am getting 60, 18 and 30 for the green, yellow and red respectively.

 

[johnbrownbaby]

Thanks for your input. I changed the code as per your suggestion. The total column is still showing up the wrong numbers. I am getting 60, 18 and 30 for the green, yellow and red respectively.

Correction: The numbers that I get for the totals are 94, 34 and 50. The actual total should be 92, 32 and 48.
Also, how to modify the code to get the subtotals from each block?

Thanks!

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Get Total of Specific Background Colored Cells Pt. 2
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[johnbrownbaby]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Get Total of Specific Background Colored Cells Pt. 2
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hello, How do I edit my post to include the link?

 

[Fluff]

If you have only asked the question on ExcelForum, then you don't need to as I have supplied the link for you this time. Just remember to supply the links yourself if you cross post in future.
If you have asked this anywhere else, then just put the relevant links in a new post.

 

[johnbrownbaby]

If you have only asked the question on ExcelForum, then you don't need to as I have supplied the link for you this time. Just remember to supply the links yourself if you cross post in future.
If you have asked this anywhere else, then just put the relevant links in a new post.

Thank you! I will be more respectful in the future. Sincerest apologies! I have not posted on any other forum besides ExcelForum.