Trying to have one cell trigger a formula calculation in one other cell

[INEEDHELPCO]

I am tring to develop a yield managment type of spread sheet where the pricing peramiters are variables. I have a data entry event in one cell casue it to calculate a formula in another cell. I need to do this for a range of cell but I want each cell in the range to correlate to one and only one cell in the results/formula range. So D7 would correlate to I7, E7 to J7, F7 to K7, G7 to L7, D8 to I8, E8 to J8, F8 to K8, G8 to L8 and so one for two seperate ranges of 84 cells each (4 accros and 21 down).
When something is entered into I7 it will trigger the event to calculate the formula in D7, something entered into J7 would calculate the formula in L7 and so on.
I have been able to get it to work with a simple formula.
Example
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target = Cells(7, 9) Then ActiveSheet.Range("D7:D7").Calculate
If Target = Cells(7, 10) Then ActiveSheet.Range("E7:E7").Calculate
If Target = Cells(7, 11) Then ActiveSheet.Range("F7:F7").Calculate
If Target = Cells(7, 12) Then ActiveSheet.Range("G7:G7").Calculate
If Target = Cells(8, 9) Then ActiveSheet.Range("D8:D9").Calculate
If Target = Cells(8, 10) Then ActiveSheet.Range("E8:E8").Calculate
and so on
End Sub

When the formula in the cell to be calculated is simple like this =B5+C5
it works.
But when I try to us this formula
=+IF(I9="",0,IF(COUNTA($I$7:$L$27)/84<=$D$35,$E$35,IF(COUNTA($I$7:$L$27)/84<=$D$36,$E$36,IF(COUNTA($I$7:$L$27)/84<=$D$37,$E$37,IF(COUNTA($I$7:$L$27)/84<=$D$38,$E$38,IF(COUNTA($I$7:$L$27)/84<=$D$39,$E$39,0)))))
which is the one I want it works up until a threashold in the IF Then statement is hit an then it changes all the values to that new number. I want it to calculate based on the data in the range at the time something is entered into the correlated cell and not recalculate it ever again unless something is enter into that sell again.
It doesn't look like it is just recalculating everyhthing everytime beacuse it works for the first stage of the IF THEN statement but then it goes crazy. So either the Recalculation is still runing for that cell - don't really think that is happening or something in the formula maybe is triggering the event.
The formula works when I manual run the formulas or when I use a macro that I manually run the range recalculation, however, when I try to do it with an event to automate the updating of the yeild management it doesn't work.
The data in yellow is tied to an event and changed from $20 to $25 when I added the put the name in the Player 1 slot. All the data that remained at $20 is not yet tied to an event trigger. It wasnt working so I didn't complete the code.
I have been working on this for a few days now and would appreciate and help I can get.
Thanks,

	Player 1	Player 2	Player 3	Player 4		Player 1	Player 2	Player 3	Player 4
6:00 AM	$25.00	$25.00	$0.00	$0.00		John	John
6:09 AM	$0.00	$0.00	$0.00	$0.00
6:18 AM	$25.00	$25.00	$25.00	$25.00		john	john	john	john
6:27 AM	$20.00	$20.00	$0.00	$0.00		john	john
6:36 AM	$20.00	$20.00	$0.00	$0.00		john	john
6:45 AM	$20.00	$20.00	$0.00	$0.00		john	john
6:54 AM	$20.00	$20.00	$0.00	$0.00		john	john
7:03 AM	$20.00	$20.00	$0.00	$0.00		john	john
7:12 AM	$20.00	$20.00	$0.00	$0.00		john	john
7:21 AM	$20.00	$20.00	$0.00	$0.00		john	john
7:30 AM	$20.00	$20.00	$0.00	$0.00		john	john
7:39 AM	$0.00	$20.00	$0.00	$0.00			john
7:48 AM	$0.00	$20.00	$0.00	$0.00			john
7:57 AM	$0.00	$20.00	$0.00	$0.00			john
8:06 AM	$0.00	$20.00	$0.00	$0.00			john
8:15 AM	$0.00	$0.00	$0.00	$0.00
8:24 AM	$0.00	$0.00	$0.00	$0.00
8:33 AM	$0.00	$0.00	$0.00	$0.00
8:42 AM	$0.00	$0.00	$0.00	$0.00
8:51 AM	$0.00	$0.00	$0.00	$0.00
9:00 AM	$0.00	$0.00	$0.00	$0.00
Available	84
Used	24
Avg/Available	$5.71
%Utilized	28.57%
Used Min%	Used Max%	Rate
0%	30%	$ 20.00
31%	40%	$ 25.00
51%	70%	$ 30.00
71%	85%	$ 35.00
86%	100%	$ 40.00

<colgroup><col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;" span="2" width="78"><col style="width: 48pt;" span="8" width="64"><tbody>
</tbody>

 

[AlphaFrog]

Try this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Excel.Range)
    
    [color=darkblue]If[/color] Target.Cells.Count = 1 [color=darkblue]Then[/color]
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("I7:L28"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            Target.Offset(, -5).Calculate
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[INEEDHELPCO]

Thanks AlphaFrog!
It worked perfectly.
Have a great day!

 

[INEEDHELPCO]

AlphaFrog,
Is there a way to have it count a clear or deletion of data in a cell within the range, so it updates the corresponding cell?
Thanks

 

[INEEDHELPCO]

AlphaFrog,
Please accept my appologies and gratitude. Not sure why but it didn't appear to be wokring when I deleted or cleared the contents of a cell, however, it is working perfeclty now.
You are the best, thanks so much.