[RESOLVED] set value of cell to 0 based on value of another cell in a TABLE range

[scoobster]

Hi

I have a TABLE range which has 3 columns. Column 1 is called 'Product', Column 2 is called 'Price' and column 3 is called 'Discount'.

I only want a value in one of these cells for each product so basically a user cannot have both a price and discount.

I need some code that will set the discount value to 0 on the same row if the user has entered a price value. Same if the user has entered a value in the discount column - I want the price on the same row to be set to 0.

I am thinking of using the Worksheet_Change event for this so that the update happens immediately.

Thanks for any suggestions on how to best go about this.

[westconn1]

using the Worksheet_Change event

try like

if target.column= 2 and not target = 0 then 
      target.offset(,1) = 0
elseif target.column = 3 and not target = 0 then
   target.offset(, -1) = 0
end if

[scoobster]

Excellent code. Worked first time and does exactly what I was looking for.

Thanks a lot.

[scoobster]

Sorry to come back on this but I have a small issue with the slighly modified code.

I have validation setup on cells in column 6 such that I can only enter a number between 0 and 100. When I enter, say, 100.5 then I get a validation failed message but if I accidently press ESC key then I get error "1004 Application Defined or object defined" on the line;

    Target.Offset(, 1) = 0

Full code is below;

Private Sub Worksheet_Change(ByVal Target As range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub  'if multiple cells are selected then exit to avoid an error

If Target.Column = 6 And Not Target = 0 Then        'if discount column has been updated and is not 0 then set price cell to 0
    Target.Offset(, 1) = 0
Else
    If Target.Column = 7 And Not Target = 0 Then    'if price column has been updated and is not 0 then set discount cell to 0
        Target.Offset(, -1) = 0
    End If
End If

End Sub

Any idea how to trap / stop this error?

Thanks