Forcing data value in a cell

T

Trying2Learn

Hello
I have a question. I have a spreadsheet with three columns. Only one of the
columns must contain 1 and other two will be then 0. I need to automate the
following tast:

If I input 1 in one cell (of any of the three columns), then others two
cells should automatically change their values to 0.

Any help in this matter will be appreciated.
Thanks.

Trying2Learn
 
S

Stefi

Try this event routine (it works with columns A,B,C):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
forcevalue = IIf(Target.Value = 1, 0, 1)
c = Target.Column
r = Target.Row
Select Case c
Case 1
Cells(r, 2).Value = forcevalue
Cells(r, 3).Value = forcevalue
Case 2
Cells(r, 1).Value = forcevalue
Cells(r, 3).Value = forcevalue
Case 3
Cells(r, 1).Value = forcevalue
Cells(r, 2).Value = forcevalue
End Select
Application.EnableEvents = True
End Sub

Warning: this macro assumes that you enter only 0 or 1.

Regards,
Stefi


„Trying2Learn†ezt írta:
 
Top