SUM with minus if cell is colored

[ride2esc]

Hello
Weird question.
If a cell is colored, can the sum calculate with minus?
I am attaching an example.
For example a grocery list, at the bottom I have SUM
But while items are purchased, I color the cells.
Instead of SUM I need it with minus.
Thank you

 

[Herakles]

Have a seperate column to indicate that the item has been purchased.

Use conditional formatting to color the cell.

Use a SUMIF formula to calculate the value where items have been purchased.

 

[ride2esc]

I am not an expert.. can you give me an excel demo with this example, please?

 

[Herakles]

Does this help? The Conditionally Formatting rule type is 'Use a formula to determine which cells to format'.
Your cell ranges may be different to mine.

 

[ride2esc]

Hello
and thank you for trying to help me.
This doesn't help me ;(
I need it with color...

 

[Herakles]

Sadly, there is no event to capture a cell color change unlike capturing a cell value change.

What one can do is check the colours from the Worksheet_SelectionChange event and then update the total.

If a color is changed the event code would need to be called by selecting another cell in the Amounts
column. This will recalculate the total.

This code needs to be placed in the worksheet code module. Right-click on the worksheet tab and select View Code.
You will need to change the "C6:C14" code as appropriate.

Are the number of fruits likely to change?

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngData As Range
Dim indRefColor As Long
Dim rng As Range
Dim dblTotal As Double
Dim rngIntersect As Range

        If Target.CountLarge > 1 Then
            Exit Sub
        End If
        
        Set rngData = Range("C6:C14")
    
        Set rngIntersect = Intersect(rngData, Target)
        
        If Not rngIntersect Is Nothing Then
    
            indRefColor = Cells(1, 1).Interior.Color
        
            For Each rng In rngData.Cells
                If rng.Interior.Color <> indRefColor Then
                    dblTotal = dblTotal + rng.Value
                End If
            Next rng
            
            Range("C15").Value = -dblTotal
     
        End If
     
End Sub

 

[ride2esc]

Thank you
With all the respect, can you send me the excel demo with all this VBA code in it?