How to change other cell value

[treeppm]

Hello fellows. I have a doubt on Excel calculation.

If I change the A1 value the B1 should automatically change to 1
the same if suppose I change B1 value the A1 should change to 1

Kind Regards

 

[veejayc]

I think it will create circular reference.

 

[treeppm]

No bro., It will not circulate. It'll be simple. But I don't know the syntax. You see in all programming languages logic is same. but we have to know the syntax.
I think "=SetCellValue(target_cell, value)" may be the part of the solution. but I don't know the full syntax

 

[mole999]

for that to work you use a formula, but when you put a value in 1 cell it overwrites the formula

 

[treeppm]

Sorry I missed to say full story. wait will update here
Kind Regards

 

[Akuini]

You can use vba.
Put the code in the code module of the sheet in question.

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A1:B1"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]If[/COLOR] Target.Cells.Count = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        [COLOR=Royalblue]If[/COLOR] Target.Column = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] Target.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] Target.Column = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Then[/COLOR] Target.Offset([COLOR=crimson]0[/COLOR], -[COLOR=crimson]1[/COLOR]) = [COLOR=crimson]1[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[treeppm]

Sorry for my previous unclear questions.
Here is the well explained in detail question. in the beginning the values will be like this

the value of F1=A1*A2*C1*E1
When I change the A1 value to >1 it'll give result like this

and If I change the value of C1 the rest of the value A1 and E1 should change to 1 and It should give result like this

The same for E1 Too.. If I tried to change the value of E1 to >1 the A1,C1 should change to 1

Kind Regards

 

[Akuini]

Did you try my code for your first scenario (changing value between A1 & B1)?

 

[treeppm]

Did you try my code for your first scenario (changing value between A1 & B1)?

Bro, it's using range value., but I use some values between B1,C1 so it affects the whole thing. Kindly again write code for post #7
So I can get Idea.

Kind Regards

 

[Akuini]

Bro, it's using range value., but I use some values between B1,C1 so it affects the whole thing. Kindly again write code for post #7
So I can get Idea.

Kind Regards

Try this:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A1,C1,E1"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]If[/COLOR] Target.Cells.Count = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]And[/COLOR] Target.Value > [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR]
       
            Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
                [COLOR=Royalblue]Select[/COLOR] [COLOR=Royalblue]Case[/COLOR] Target.Column
                    [COLOR=Royalblue]Case[/COLOR] [COLOR=crimson]1[/COLOR]
                    Target.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]2[/COLOR]) = [COLOR=crimson]1[/COLOR]
                    Target.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]4[/COLOR]) = [COLOR=crimson]1[/COLOR]
                    [COLOR=Royalblue]Case[/COLOR] [COLOR=crimson]3[/COLOR]
                    Target.Offset([COLOR=crimson]0[/COLOR], -[COLOR=crimson]2[/COLOR]) = [COLOR=crimson]1[/COLOR]
                    Target.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]2[/COLOR]) = [COLOR=crimson]1[/COLOR]
                    [COLOR=Royalblue]Case[/COLOR] [COLOR=crimson]5[/COLOR]
                    Target.Offset([COLOR=crimson]0[/COLOR], -[COLOR=crimson]4[/COLOR]) = [COLOR=crimson]1[/COLOR]
                    Target.Offset([COLOR=crimson]0[/COLOR], -[COLOR=crimson]2[/COLOR]) = [COLOR=crimson]1[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Select[/COLOR]
            Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
 
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]