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.

 

[nsipes]

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

That sort of works. The user will click on the cells in row 4, columns B-F, the cell they click on will turn red, then it needs to put a total for that cells value in Column G, it needs to score five rows (4, 6, 8, 10, and 12). I put the number value for each cell column above the column. Does that help?

 

[nsipes]

I REALLY appreciate your help!!!!

 

[AlphaFrog]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Range("B4:F12"), Target) Is Nothing And Target.Row Mod 2 = 0 Then
        Cancel = True
        With Range("G" & Target.Row)
            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

 

[nsipes]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Range("B4:F12"), Target) Is Nothing And Target.Row Mod 2 = 0 Then
        Cancel = True
        With Range("G" & Target.Row)
            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

Oh my goodness!!!! Bless you heart!!!! Now I need to add the other 8 standards, would you make each standard a separate sheet so the same coding works on each individual sheet? How do I pay you for this?

 

[AlphaFrog]

You're welcome. No need to pay me. Just pay it forward.

You can use one sheet or multiple,. It's up to you. You would just need to tweak the range of cells (if different) in this line of code.

If Not Intersect(Range("B4:F12"), Target) Is Nothing And Target.Row Mod 2 = 0 Then

 

[nsipes]

You're welcome. No need to pay me. Just pay it forward.

You can use one sheet or multiple,. It's up to you. You would just need to tweak the range of cells (if different) in this line of code.

If Not Intersect(Range("B4:F12"), Target) Is Nothing And Target.Row Mod 2 = 0 Then

Do you have advise for how I can learn Visual Basic better. I knew what I wanted it to do, just couldn't figure out the coding. YOU are awesome!! I will definitely pay it forward!

 

[AlphaFrog]

Best way to learn VB is to actually use VB and write code. Breakdown your task into small steps and then web search each step for VB code e.g. how to determine even numbered rows: Target.Row Mod 2 = 0 .

Read the provided solution code above and figure out what each line does. If you can do that, then you're one step closer.

I got good at VB coding by reading\answering questions on this forum.

 

[nsipes]

Best way to learn VB is to actually use VB and write code. Breakdown your task into small steps and then web search each step for VB code e.g. how to determine even numbered rows: Target.Row Mod 2 = 0 .

Read the provided solution code above and figure out what each line does. If you can do that, then you're one step closer.

I got good at VB coding by reading\answering questions on this forum.

Thank you so much!!!