automate Copy and paste into static value

EDIT : posted in wrong post. Sorry

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
    For Each cell In Range("C3:C10000")
        If cell = "" Then
        cell.Value = Range("B2").Value
        Exit For
        End If
    Next
End If
End Sub 


Originally Posted by jindon

To Sheet1 code module

Hi Jindon,
Yours and mine, both do not work, if B2 has formula. For instant, B2=A1+B1
If A1 or B1 changed, B2 change value, but worksheetchange evens could not fire it.
Any idea?

Actually, I am doing a workbook to calculate golf handicap index.
Everything works, except i can't find a way to sort out the Exceptional Scores Reduction calculation.
The Exception Scores Reduction is -1 if player gets a differential (diff) 7-10 better than his/her HI at the time playing the game (today HI), and -2 if it's >10 points better. And the ESR is cumulative to the previous 20 scores, if any.

So there are 2 difficulties that stuck me here (please at the Latest20scores sheet):
1) the today HI will be changing after every game (so i was thinking to copy static to a target cell and use static value in the target cell to calculate for the ESR. I can't figure how to automate copy and paste value into static)
2) the ESR -1 or -2 will be moving down the row correspondingly when there will be new score adding in (score added in Scores sheet will automatically be added on the first row of the 20 row diff calculation (row 13 in the sheet) to calculate the next diff and HI. So, 19/11/2021, player had a score of 84, which gave a Diff of 7-10 better than the HI at the time he played that game, so -1, and on 20/11/2021, he got a score of 75, which again gave a Diff >10 points better than the HI at the time he was playing.

I think if I could make Excel automatically copy the Today HI and paste it into static value in a way like I copy it MANUALLY into a target cell (highlighted in yellow), then I could use them to calculate the ESR.
Wonder if anyone has better idea?

*Note that I hide all other players and show one player

thanks a bunch
txt007