Counting a sign up list & if paid

[SorenN]

Hi Everybody!
I have a list of names for an event people need to sign up & pay for. In an Ideal world, I would like excel to count if somebody is on the list, and also if they have paid. Right now I'm sorting this by text color and manually counting. I'm not sure if I need to create Macro (which I know only a little about) or if there is a way to have Excel count based on cell formatting. I am open to new ideas and suggestions! Also if I am asking for too much, I can settle partial or simpler results.
Thanks in advance for your help!
-Soren

In table green means they paid, red they still owe and black is I think they are coming.
If I change Nico's name to red, what do I need to do to have the Total owed increase (by $15)? Or if I change Dante to green how can I have both Total Paid and Total Owed increase?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Total Paid[/TD]
[TD]Robert
[/TD]
[TD]Nico[/TD]
[/TR]
[TR]
[TD]$45[/TD]
[TD]Soren
[/TD]
[TD]Dante[/TD]
[/TR]
[TR]
[TD]Total owed[/TD]
[TD]Leslie
[/TD]
[TD]Elora[/TD]
[/TR]
[TR]
[TD]$75[/TD]
[TD]Laura
[/TD]
[TD]Ian
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

If you have your data as follows.
We can use a macro to add the totals.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">TOTALS</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">NAMES</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">NAMES</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Total Paid</td><td style="color:#00ff00; ">Robert</td><td >Nico</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">$45</td><td style="color:#00ff00; ">Soren</td><td >Dante</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Total owed</td><td style="color:#00ff00; ">Leslie</td><td >Elora</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">$75</td><td style="color:#ff0000; ">Laura</td><td style="color:#ff0000; ">Ian</td></tr></table>

Put the following code in the events of your sheet.

Change the data in red for your information, but before making changes, try the macro with the example that I am sending you.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, c As Range
    Set r = Range("[COLOR=#ff0000]B2:C2[/COLOR]", Range("B" & Rows.Count).End(xlUp)) 'Cells with names
    Range("[COLOR=#ff0000]A3[/COLOR]").Value = 0   'cell total paid
    Range("[COLOR=#ff0000]A5[/COLOR]").Value = 0   'cell total owed
    For Each c In r
        Select Case c.Font.ColorIndex
            Case 4  'verde
                Range("[COLOR=#ff0000]A3[/COLOR]").Value = Range("[COLOR=#ff0000]A3[/COLOR]").Value + 15
            Case 3  'rojo
                Range("[COLOR=#ff0000]A5[/COLOR]").Value = Range("[COLOR=#ff0000]A5[/COLOR]").Value + 15
            Case 1  'negro
                Range("[COLOR=#ff0000]A5[/COLOR]").Value = Range("[COLOR=#ff0000]A5[/COLOR]").Value + 15
        End Select
    Next
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

---
Note:
Changing the color of the letter of a cell does not activate any event on the sheet. Therefore, after changing a color, you must select any cell and the macro will be executed, recalculating the totals.

 

[SorenN]

Thanks for the help Dante.
I followed your advice and tried it with the sample provided. This seems to be close, although it doesn't seem to work for all the cells.
For example I tried changing 'Laura' from red to green and total paid went up, total owed stayed the same. Also, when I changed Dante from black to red no values changed.
What do we try next, or am I asking for too much?
-Soren

 

[DanteAmor]

The green color should be standard green, like red and black.


Do the following
select a cell with the green text and execute this macro:

Sub tes()
    MsgBox Selection.Font.ColorIndex
End Sub

The number in the message you put in this line

Case 4  'verde

Repeat the same for red and black colors

 

[SorenN]

Thanks for that tip Dante.
I put the number from the macro in

case 4

and it doesn't seem to be working for the total paid. Out of curiosity I tried putting that font color index number in

case 1

and it would change the total owed. It seems I am using the same color for my font as is listed in the code. I am not sure why it is not changing the total paid.
Here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, c As Range
    Set r = Range("B2:C2", Range("B" & Rows.Count).End(xlUp)) 'Cells with names
    Range("A3").Value = 0   'cell total paid
    Range("A5").Value = 0   'cell total owed
    For Each c In r
        Select Case c.Font.ColorIndex
            Case 4  '14
                Range("A3").Value = Range("A3").Value + 15
            Case 3  '3
                Range("A5").Value = Range("A5").Value + 15
            Case 1  '1
                Range("A5").Value = Range("A5").Value + 15
        End Select
    Next
End Sub

Can you think of something else I can try?

 

[DanteAmor]

Thanks for that tip Dante.
I put the number from the macro in

case 4

and it doesn't seem to be working for the total paid. Out of curiosity I tried putting that font color index number in

case 1

and it would change the total owed. It seems I am using the same color for my font as is listed in the code. I am not sure why it is not changing the total paid.
Here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, c As Range
    Set r = Range("B2:C2", Range("B" & Rows.Count).End(xlUp)) 'Cells with names
    Range("A3").Value = 0   'cell total paid
    Range("A5").Value = 0   'cell total owed
    For Each c In r
        Select Case c.Font.ColorIndex
            Case 4  '14
                Range("A3").Value = Range("A3").Value + 15
            Case 3  '3
                Range("A5").Value = Range("A5").Value + 15
            Case 1  '1
                Range("A5").Value = Range("A5").Value + 15
        End Select
    Next
End Sub

Can you think of something else I can try?

total paid color green sum in A3
total owed color red and black sum in A5, is that correct?