Need running total formula based on adjacent column text color

[chad7469]

I need to create a running total based on the color of the text in an adjacent column. I am using 2007 Excel. Essentially, every time I enter a date in "black" text, I need the count to go up by "one"; every time I enter a date in "red" text, I need the count to go down by "one". I need the column titled "Net 15 Rule" to contain the running total, based on the color of the date, as depicted below.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Original Date[/TD]
[TD]Net 15 Rule[/TD]
[/TR]
[TR]
[TD]8/29/2012[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/2/2012[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9/5/2012[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/9/2012[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9/10/2012[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9/15/2012[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9/18/2012[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]



I apologize if I did not format my question properly or use correct terminology, I am rather new to Excel. If you need further information to answer my question, please let me know.

Thanks for any help!!!!!!!!!!

 

[Reggie74]

Have a look at this .... (this code is to be inserted in the command button)

Private Sub CommandButton1_Click()
    
   Range("A3").Select
   
   Do While ActiveCell.Value <> ""
        If ActiveCell.Font.ColorIndex = 3 Then                                           'if font colour of date is red
            ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1) - 1
        Else                                                                                            'if font colour of date is black
            ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1) + 1
        End If
        
        ActiveCell.Offset(1, 0).Select
   Loop
   
End Sub

<---- Click on image


Note that the loop starts at "A3" BUT you can change it depending on your starting range and you don't have to complete the rest of the entries under "Net 15 Rule". The macro does this

Hope this helps ?

I need to create a running total based on the color of the text in an adjacent column. I am using 2007 Excel. Essentially, every time I enter a date in "black" text, I need the count to go up by "one"; every time I enter a date in "red" text, I need the count to go down by "one". I need the column titled "Net 15 Rule" to contain the running total, based on the color of the date, as depicted below.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Original Date[/TD]
[TD]Net 15 Rule[/TD]
[/TR]
[TR]
[TD]8/29/2012[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/2/2012[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9/5/2012[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/9/2012[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9/10/2012[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9/15/2012[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9/18/2012[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I apologize if I did not format my question properly or use correct terminology, I am rather new to Excel. If you need further information to answer my question, please let me know.

Thanks for any help!!!!!!!!!!