Macro to sum cells based on fill color and text color

[hootie]

I am trying to come up with a macro to sum the below data based on cell color and text color but am having issues. The data is in cells B2:D45 and the different fill colors are blue and red. The different text colors are black and yellow. Any help would be appreciated.
Thanks,

 

[Michael M]

Re: Help with Macro to sum cells based on fill color and text color

Don't see any data....but have a look at this previous thread !!!


https://www.mrexcel.com/forum/excel-questions/991391-vba-sum-cells-color.html

 

[My Aswer Is This]

Re: Help with Macro to sum cells based on fill color and text color

Assuming it's:
Blue Fill and Black Font and
Red Fill and Yellow Font
A Message Box will popup with your totals
Try this:

Sub Sum_Range()
Application.ScreenUpdating = False
Dim c As Range
Dim r As Long
Dim b As Long
Dim ans As String
Dim bb As String
Dim rr As String
    
    For Each c In Range("B2:D45")
        If c.Interior.Color = vbBlue And c.Font.Color = vbBlack Then b = b + c.Value
        If c.Interior.Color = vbRed And c.Font.Color = vbYellow Then r = r + c.Value
    Next
    ans = "Your totals are"
    bb = "Blue and Black total =  " & b
    rr = "Red and Yello total =  " & r
    
    MsgBox ans & vbNewLine & bb & vbNewLine & rr
Application.ScreenUpdating = True
End Sub

 

[hootie]

Re: Help with Macro to sum cells based on fill color and text color

Below is the code I am currently using to sum based on fill color and then I am using the formula "=sumcolour($B$2:$D$45,A1)". In cell A1 I have the fill color that I am trying to sum for. Is there a way to modify this so that it also looks for text color?

Function SumColour(rng As Range, clr As Range)
 Application.Volatile
 Dim c As Range
 For Each c In rng
     If c.Interior.ColorIndex = clr.Interior.ColorIndex _
     And IsNumeric(c) Then
         SumColour = SumColour + c.Value
     End If
 Next
 End Function

 

[My Aswer Is This]

Re: Help with Macro to sum cells based on fill color and text color

I provided a script which checks for interior color and font color like you asked for. You did not mention if you tried my script. So not sure why your now asking for me to modify your script which you did not mention before to do what you want so I'm confused.

 

[hootie]

Re: Help with Macro to sum cells based on fill color and text color

My apologies. I did try it but yours gave a message box answer and I am trying to have it show up as part of a formula so that the user of this spreadsheet will see the answer without having to run the script. I should have been more specific with my original post of what I was looking for.

 

[My Aswer Is This]

Re: Help with Macro to sum cells based on fill color and text color

My apologies. I did try it but yours gave a message box answer and I am trying to have it show up as part of a formula so that the user of this spreadsheet will see the answer without having to run the script. I should have been more specific with my original post of what I was looking for.

Someone else here at Mr. Excel will need to help you. I have no knowledge of how to do this as part of a formula. I will continue to monitor this thread and see what I can learn.