Formula or Macro help - using colours to get a total sum??

CarolSweet 

If you want to assign values to colors in Excel and then calculate the total cost based on those colors, you might consider using a combination of Conditional Formatting and helper columns with formulas or VBA.

Here is a step-by-step guide:

Method 1: Using Conditional Formatting and Helper Columns

1. Assign Values to Colors:
• Choose a range of cells where you will be assigning colors and values.
• Apply Conditional Formatting to those cells. Set up rules for each color and assign a unique number to each rule.
2. Helper Column:
• In an adjacent column, use a formula to look up the values based on the color using the CELL and GET.CELL functions.

=IFERROR(CHOOSE(GET.CELL(38, A1), 10, -- Value for Color1, 20, -- Value for Color2, 30 -- Value for Color3 ), 0)

• Adjust the cell references and values accordingly.
1. Calculate Total:
• Use a SUM formula to calculate the total based on the helper column.

=SUM(B:B)

Method 2: Using VBA (Visual Basic for Applications)

1. Insert Module:
• Press Alt + F11 to open the VBA editor.
• Insert a new module: Insert -> Module.
2. VBA Code:
• Use the following VBA code and modify it according to your color-value pairs.

Vba code:

Function GetColorValue(rng As Range) As Double
    Select Case rng.Interior.Color
        Case RGB(255, 0, 0): GetColorValue = 10   ' Red
        Case RGB(0, 255, 0): GetColorValue = 20   ' Green
        Case RGB(0, 0, 255): GetColorValue = 30   ' Blue
        Case Else: GetColorValue = 0
    End Select
End Function

Use in Worksheet:

• Back in Excel, use the following formula in a cell to get the value for a given colored cell:

=GetColorValue(A1)

• Modify the cell reference as needed.
1. Calculate Total:
• Use the SUM function to calculate the total based on the helper column.

Both methods allow you to assign values to colors and calculate totals based on those values. Choose the one that fits your requirements and comfort level with formulas or VBA. The text and steps were edited 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.