Forum Discussion

Copper Contributor

Oct 17, 2023

Create a conditional sum of cell contents based on cell color

How to create a numerical sum of cells if cell color fill = 'No Fill" or if cell color fill is a certain color such as yellow?

Excel

Formulas and Functions

NikolinoDE
Oct 18, 2023
Hugh_Wilbarger
In Excel, you can create a conditional sum of cell contents based on cell colors using a combination of Visual Basic for Applications (VBA) and a User-Defined Function (UDF). Here's how you can do it:
Step 1: Enable Developer Tab If you haven't already enabled the Developer tab in Excel, do the following:
Go to the "File" tab.
Click on "Options."
In the Excel Options dialog, select "Customize Ribbon."
Check the "Developer" option in the right pane.
Click "OK."
Step 2: Create a User-Defined Function (UDF) in VBA
Press ALT + F11 to open the VBA editor.
Click "Insert" in the menu and select "Module" to insert a new module.
Copy and paste the following VBA code into the module window:
vba code:
Function SumByColor(rng As Range, cellColor As Range) As Double Dim cell As Range Dim total As Double Application.Volatile For Each cell In rng If cell.Interior.Color = cellColor.Interior.Color Then total = total + cell.Value End If Next cell SumByColor = total End Function
Close the VBA editor.
Step 3: Use the UDF in Excel
Now, you can use the UDF in your Excel worksheet to sum cells based on their color. Assuming you have a range of numbers in cells A1:A10 and you want to sum the cells with a yellow background:
In a different cell, type the following formula:
=SumByColor(A1:A10, B1)
A1:A10 is the range you want to sum.
B1 refers to the cell containing the color you want to match (B1 should have the same color as the cells you want to sum).
Press Enter. The formula will sum the values in cells with the same background color as the color in cell B1.
This formula will sum the values in the specified range based on the cell color, which can be either "No Fill" or any specific color you choose. You can customize it to match other colors by changing the cellColor reference to another cell with the desired color or by specifying the color as an RGB value. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!
This will help all forum participants.

NikolinoDE

Gold Contributor

Hugh_Wilbarger

In Excel, you can create a conditional sum of cell contents based on cell colors using a combination of Visual Basic for Applications (VBA) and a User-Defined Function (UDF). Here's how you can do it:

Step 1: Enable Developer Tab If you haven't already enabled the Developer tab in Excel, do the following:

Go to the "File" tab.
Click on "Options."
In the Excel Options dialog, select "Customize Ribbon."
Check the "Developer" option in the right pane.
Click "OK."

Step 2: Create a User-Defined Function (UDF) in VBA

Press ALT + F11 to open the VBA editor.
Click "Insert" in the menu and select "Module" to insert a new module.
Copy and paste the following VBA code into the module window:

vba code:

Function SumByColor(rng As Range, cellColor As Range) As Double
    Dim cell As Range
    Dim total As Double
    Application.Volatile

    For Each cell In rng
        If cell.Interior.Color = cellColor.Interior.Color Then
            total = total + cell.Value
        End If
    Next cell

    SumByColor = total
End Function

Close the VBA editor.

Step 3: Use the UDF in Excel

Now, you can use the UDF in your Excel worksheet to sum cells based on their color. Assuming you have a range of numbers in cells A1:A10 and you want to sum the cells with a yellow background:

In a different cell, type the following formula:

=SumByColor(A1:A10, B1)

A1:A10 is the range you want to sum.
B1 refers to the cell containing the color you want to match (B1 should have the same color as the cells you want to sum).

Press Enter. The formula will sum the values in cells with the same background color as the color in cell B1.

This formula will sum the values in the specified range based on the cell color, which can be either "No Fill" or any specific color you choose. You can customize it to match other colors by changing the cellColor reference to another cell with the desired color or by specifying the color as an RGB value. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

laurieirish

Copper Contributor

Jun 17, 2024

NikolinoDE It doesn't seem to recognize cell colors that are created via conditional formatting

NikolinoDE

Gold Contributor

Jun 18, 2024

laurieirish

Create a User-Defined Function (UDF) in VBA

Press ALT + F11 to open the VBA editor.
Click "Insert" in the menu and select "Module" to insert a new module.
Copy and paste the following VBA code into the module window:

Vba Code is untested backup your file.

Function SumByColor(rng As Range, cellColor As Range) As Double
    Dim cell As Range
    Dim total As Double
    Dim color As Long

    Application.Volatile

    ' Get the color of the reference cell
    color = cellColor.DisplayFormat.Interior.Color

    For Each cell In rng
        ' Check if the cell's display format color matches the reference color
        If cell.DisplayFormat.Interior.Color = color Then
            total = total + cell.Value
        End If
    Next cell

    SumByColor = total
End Function

Step 3: Use the UDF in Excel

Now, you can use the UDF in your Excel worksheet to sum cells based on their display color (including conditional formatting):

Assuming you have a range of numbers in cells A1:A10 and you want to sum the cells with a yellow background:
In a different cell, type the following formula:

=SumByColor(A1:A10, B1)

laurieirish
Copper Contributor
Jun 19, 2024
My humble thanks !!

Joe_M

Copper Contributor

Oct 04, 2024

NikolinoDE

Hope this makes sense.

Looking for a macro that would take column B (date) and add days determined by color.

Color purple would be +30days

all other colors would be +90 days

Column B received date (color to denote due date)

Column C due date

thank you in advance

NikolinoDE

Gold Contributor

Oct 04, 2024

Joe_M

The code is untested, please backup your file beforehand.

Sub AddDaysBasedOnColor()
    Dim ws As Worksheet
    Dim cell As Range
    Dim receivedDate As Date
    Dim dueDate As Date
    Dim purpleColor As Long
    
    ' Define the purple color code (you can adjust this if your purple is different)
    purpleColor = RGB(128, 0, 128) ' This is the RGB value for purple
    
    ' Set the worksheet you are working with
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
    
    ' Loop through each cell in Column B
    For Each cell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
        ' Check if the cell is not empty
        If IsDate(cell.Value) Then
            receivedDate = cell.Value
            
            ' Check the color of the cell
            If cell.Interior.Color = purpleColor Then
                ' Add 30 days for purple
                dueDate = receivedDate + 30
            Else
                ' Add 90 days for all other colors
                dueDate = receivedDate + 90
            End If
            
            ' Place the due date in Column C
            cell.Offset(0, 1).Value = dueDate
        End If
    Next cell
    
    MsgBox "Due dates updated based on cell colors!"
End Sub

My answers are voluntary and without guarantee!

Hope this will help you.

ae2025
Copper Contributor
Jan 07, 2025
Works great now with conditional formatting. Thanks!

Forum Discussion

Create a conditional sum of cell contents based on cell color

Share

Resources