Q:

Dynamically detect change in range and add or remove formula in cell in excel

I have a dynamic range of cells. Depending on if someone adds or deletes data in one of the cells in column A, the range will change. I need to detect a change in that range. The range is equal to
 sht.Range("C3:" & Cells(numberOfRows, numberOfColumns).
Then, for example, let's say the cell that changed is "F9". I need to add a formula to "AH9". The point here is that the formula will always be in the "AH" column and the row is what will change. In case you are curious, the formula I am adding will sum columns "C" to "AG" in the row that has the changed cell. So, if "F9" is the cell that changed, I need to sum "C9" to "AG9" =SUM(F9:AG9). I am thinking I would need to put the code in the worksheet I am trying to detect the change in and it would look something like this.
Private Sub currentSheetChange(ByVal Target As Range)

Dim sht As Worksheet
Dim rfRange As Range
Dim numberOfColumns As Integer
Dim numberOfRows As Integer

numberOfColumns = getNumberOfColumns
numberOfRows = getNumberOfRows

Set sht = ThisWorkbook.Sheets("Current")
Set rfRange = sht.Range("C3:" & Cells(numberOfRows, numberOfColumns)

If Not Intersect(Target, sht.Range(rfRange)) Is Nothing Then

This is as far as I have made it so far. If it works to this point, I am not sure how to know which cell changed. I also assume I would use sht.Range("AH" & rowNumber).Formula = to enter the formula in the cell.

A:

This allows me to enter formulas into the cells dynamically based on the row of the cell that was changed.
I decided to change my requirements slightly. I did not want the formula entered into the cell because someone could change it. I had VBA do the math and place the results into the cell. I left the new code commented out since that did not answer my original question but I wanted to show both ways so there are options.
Private Sub Worksheet_Change(ByVal Target As Range)

Dim numberOfColumns As Long
Dim numberOfRows As Long

numberOfColumns = getNumberOfColumns 'Function call to a function on another sheet
numberOfRows = getNumberOfRows 'Function call to a function on another sheet

If Not Intersect(Target, Me.Range("C3", "AG" & numberOfRows)) Is Nothing Then
    Me.Range("AH" & Target.Row).Formula = "=SUM(C" & Target.Row & ":" & "AG" & Target.Row & ")"
    'Range("AH" & Target.Row).Value = Range("B" & Target.Row).Value - Evaluate("SUM(C" & Target.Row & ":AG" & Target.Row & ")") **New code**
End If

End Sub

