Resetting value in a cell to zero if another cell in the same row is selected and changed by the user

[Bobo509]

I have a sheet where I want to calculate a price in a range of cells (i18:i26) when a percentage value is entered in range (b18:b26).

If the user then wishes to input a price in range (i18:i26) I want the value of the cell in column b of the same row to change to zero.

The code below calculates my price when percentage is entered in range ColumnB but I cannot get the percentage to change to zero if I manually input a price. This means that when I input a percentage in another row the price I just changed resets according to the formula.

Any suggestions as how to achieve this would be greatly appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("b18:b26")) Is Nothing Then Exit Sub


Application.EnableEvents = False
ActiveSheet.Range("$i18:$i26").Formula = "=IF($b18>0,$g18-($g18*$b18),0)"
Application.EnableEvents = True


If Target.Cells = ("i18:i26") Then
       Target.Offset(0, -7).Value = 0
       End If


End Sub

 

[Snakehips]

Bobo509, welcome.

Sorry just re-read post.
Will re-post solution shortly.

 

[Snakehips]

Maybe like....

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub
'Entering percentage
If Not Intersect(Target, Range("b18:b26")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 7) = Target.Offset(0, 5) * (1 - Target)
Application.EnableEvents = True
Exit Sub
End If
'Entering base price
If Not Intersect(Target, Range("g18:g26")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 2) = Target * (1 - Target.Offset(0, -5))
Application.EnableEvents = True
Exit Sub
End If
'entering an override price
If Not Intersect(Target, Range("i18:i26")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, -7) = 0
Application.EnableEvents = True
Exit Sub
End If






End Sub

Hope that helps.

 

[Bobo509]

Thank you @Snakehips - this is exactly what I needed - much appreciated! As a newbie to VBA I was struggling - this will really help in my understanding and problem solving future issues.

Thanks again!