VBA Subtraction formula on change event

[n3w2excel]

I am very new to vba but I am stuck trying to get a subtraction when a cell is entered.
For example I want in column A to do =B10-G10 but I want this on any row not specific to row 10
This is mainly to count how many days between 2 dates but it will change based on when Column G or B is changed.

I have the below but it doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

If Target.Column = 1 Then
Cells(Target.Row, 2) - Cells(Target.Row, 6))
End If
End If
End Sub

 

[Scott T]

This is not a valid line it will also only run if column A is change which is where you want your difference. What do you want to trigger the code?

Cells(Target.Row, 2) - Cells(Target.Row, 6))

This it should be something like this.

 Cells(Target.Row, 1) = Cells(Target.Row, 2) - Cells(Target.Row, 6)

 

[n3w2excel]

Thanks for this.

If the date is changed in either Column B or Column G the formula will update in column A, for example in column A if I were to write a formula it would be =IF(ISBLANK(B2),"",IF(ISBLANK(2),"Awaiting Date",B2-G2)). The number would change depending on what the date is changed too but show this.

 

[Scott T]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
 If (Target.Column = 2 Or Target.Column = 7) And (Cells(Target.Row, 2) <> "" And Cells(Target.Row, 7) <> "") Then
    Application.EnableEvents = False
    Cells(Target.Row, 1) = Cells(Target.Row, 2) - Cells(Target.Row, 7)
    Application.EnableEvents = True
 End If

If (Target.Column = 2 Or Target.Column = 7) And (Cells(Target.Row, 2) = "" Or Cells(Target.Row, 7) = "") Then
    Cells(Target.Row, 1) = "Awaiting Date"
End If
 End Sub

 

[n3w2excel]

Thanks this works perfectly.

 

[n3w2excel]

Sorry I have just noticed 1 thing this is also applying to all rows but I don't want it to only change from rows 11 down so A10 B10 and F10 are not effected. thanks

 

[Scott T]

This should work.

Private Sub Worksheet_Change(ByVal Target As Range)
 If (Target.Column = 2 Or Target.Column = 7) And (Cells(Target.Row, 2) <> "" And Cells(Target.Row, 7) <> "") And Target.Row > 10 Then
    Application.EnableEvents = False
    Cells(Target.Row, 1) = Cells(Target.Row, 2) - Cells(Target.Row, 7)
    Application.EnableEvents = True
 End If

If (Target.Column = 2 Or Target.Column = 7) And (Cells(Target.Row, 2) = "" Or Cells(Target.Row, 7) = "") And Target.Row > 10 Then
    Cells(Target.Row, 1) = "Awaiting Date"
End If
 End Sub