Totaling colored cells by their value

[nsipes]

I have a spreadsheet that I put in UDF in VBA to change a cell color when it is clicked. I also need to total those cells by which column they are in. For instance if Cell C4 (value is 2) and E3 (value is 3), and F8 and F10 (values are 4).. Is there a way to have it calculate the colored cells by those values? It is a scoring guide so values range from 0-4 depending on the cell that is clicked in each row.

 

[AlphaFrog]

One idea is to add to your VBA code a line to add that clicked on cell's address to a Named Range. Then a SUM formula that uses the Named Range (MyColoredCells in the example below) as a reference will sum the colored cells e.g.

=SUM(MyColoredCells)

 

[nsipes]

One idea is to add to your VBA code a line to add that clicked on cell's address to a Named Range. Then a SUM formula that uses the Named Range (MyColoredCells in the example below) as a reference will sum the colored cells e.g.

=SUM(MyColoredCells)

What would that code look like? I tried the sum(myColoredCells) but it returned a zero versus the score for the cell highlighted.

 

[nsipes]

What would that code look like? I tried the sum(myColoredCells) but it returned a zero versus the score for the cell highlighted.

I should explain that the score care is going to be provided to novice excel users so I need it to do everything behind the scenes.

 

[AlphaFrog]

Post your code that changes the cell color.

 

[nsipes]

This is not my coding, I copied it from one of the Mr. Excel posts (when I find it again, I will give credit where it is due)


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 4
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = IIf(Target.Interior.ColorIndex >= 3, -4142, 3)
End Sub

 

[AlphaFrog]

Need more clarification

The code you posted can color cells with two different colors depending on if you right-click or double-click. Do you want to sum all of both colors, or all of one color?

Your original post says

I also need to total those cells by which column they are in.

Do you need to sum colored cells in a specific column, or all colored cells regardless of column?

 

[nsipes]

No I only need it to total red. The cells turn red when I double click and turn back to white when I double click again. I only need one color and to be able to total the cells that are that color by their value. Column B is 0, C is 1, D is 2, E is 3 and F is 4. It needs to total the sheet. I will have 9 sheets that need to do the same process. I’d like it to transfer the scores to the Scores sheet but I can do that with a simple formula in the target sheet. Does that make sense. I did notice it had two colors but was afraid of messing it up if I adjusted it. I’m a novice on Visual Basic but getting better at understanding the code. Thank you for your help. I tried every sum and IFS formulas I could think of.

 

[nsipes]

Need more clarification

The code you posted can color cells with two different colors depending on if you right-click or double-click. Do you want to sum all of both colors, or all of one color?

Your original post says

Do you need to sum colored cells in a specific column, or all colored cells regardless of column?

Do you need to sum colored cells in a specific column, or all colored cells regardless of column?

I need them to total only the colored cells no matter which column they are in, but each column has a different value.

 

[AlphaFrog]

Replace this

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 4
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = IIf(Target.Interior.ColorIndex >= 3, -4142, 3)
End Sub

With this, and replace the G1 in the code with the cell that you want the total in.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Columns("B:F"), Target) Is Nothing Then
        Cancel = True
        With Range("G1")
            If Target.Interior.ColorIndex >= 3 Then
                Target.Interior.ColorIndex = -4142
                .Value = Application.Max(.Value - (Target.Column - 2), 0)
            Else
                Target.Interior.ColorIndex = 3
                .Value = .Value + (Target.Column - 2)
            End If
        End With
    End If
End Sub