Macro to sum cells based on fill color and text color

hootie

New Member
Joined
Feb 1, 2016
Messages
5
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,
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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:
Code:
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
 
Upvote 0
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?

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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
Back
Top