How to change other cell value

T

treeppm

Board Regular
Joined
Jun 3, 2015
Messages
60
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
 
Last edited:
Sort by date Sort by votes
Akuini said:
Try this:
Click to expand...
Perfect one. Thank you so much for the effort bro., Could you please explain this section?
Code: 
[FONT=lucida console][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][/FONT]

Kind Regards
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Target.Column is the column number of "Target" (the cell you're selecting). So if you select A1 it means Target.Column = 1, while selecting C1 means Target.Column = 3. So 'Case 1' mean if you select column 1 (col A).
So if you select A1 and you want to change value C1 & E1 then we use Offset.
Case 1
Target.Offset(0, 2) = 1 'means from A1 move 2 column to the right, i.e C1

If you don't want use column as reference you can just use the range, like this:

Code: 
[FONT=lucida console]        [COLOR=Royalblue]Select[/COLOR] [COLOR=Royalblue]Case[/COLOR] Target
        [COLOR=Royalblue]Case[/COLOR] Range([COLOR=brown]"A1"[/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] Range([COLOR=brown]"C1"[/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] Range([COLOR=brown]"E1"[/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][/FONT]
 
Upvote 0
Akuini said:
Target.Column is the column number of "Target" (the cell you're selecting). So if you select A1 it means Target.Column = 1, while selecting C1 means Target.Column = 3. So 'Case 1' mean if you select column 1 (col A).
So if you select A1 and you want to change value C1 & E1 then we use Offset.
Case 1
Target.Offset(0, 2) = 1 'means from A1 move 2 column to the right, i.e C1

If you don't want use column as reference you can just use the range, like this:

Code: 
[FONT=lucida console]        [COLOR=Royalblue]Select[/COLOR] [COLOR=Royalblue]Case[/COLOR] Target
        [COLOR=Royalblue]Case[/COLOR] Range([COLOR=brown]"A1"[/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] Range([COLOR=brown]"C1"[/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] Range([COLOR=brown]"E1"[/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][/FONT]
Click to expand...
The above code doesn't work. sometimes It replace value 1 in G1. I replaced the code and saved file then closed, reopened and start typing A1 value and changed value in C1 then it seems it replace G1 to 1
 
Upvote 0
treeppm said:
The above code doesn't work. sometimes It replace value 1 in G1. I replaced the code and saved file then closed, reopened and start typing A1 value and changed value in C1 then it seems it replace G1 to 1
Click to expand...

Hm, don't know why that happen, I can't reproduce the behavior, it works for me. :confused:
This is the modified code, right?

Code: 
[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
        [COLOR=Royalblue]Case[/COLOR] Range([COLOR=brown]"A1"[/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] Range([COLOR=brown]"C1"[/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] Range([COLOR=brown]"E1"[/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]
 
Upvote 0
Ah, actually you're right, the code has a problem, it reads the target and range as its value.
So we need to use the address property instead. Like this:

Code: 
[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.Address
                    [COLOR=Royalblue]Case[/COLOR] [COLOR=brown]"$A$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=brown]"$C$1"[/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=brown]"$E$1"[/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]
 
Upvote 0
Yo man., Great with perfect explanation. Thank you so much life saver. because the time is life. you saved my lots of time.

Kind Regards
 
Upvote 0
treeppm said:
Yo man., Great with perfect explanation. Thank you so much life saver. because the time is life. you saved my lots of time.

Kind Regards
Click to expand...

You're welcome, glad to help, & thanks for the feedback. :beerchug:
 
Upvote 0
It's not possible to treat a cell as Input and Output Cell at the same time.... In Excel it's not possible. But I am sure, it can be done with the help of VBA.
 
Upvote 0
mail2master said:
It's not possible to treat a cell as Input and Output Cell at the same time.... In Excel it's not possible. But I am sure, it can be done with the help of VBA.
Click to expand...

You guys are everywhere. You came here to say this?. Clearly I stated I want that kind of result. whether its VBA, ABA or anything.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

OldSwimmer1650
  • Question Question
Changing Cell Value on Different Tabs
Replies
4
Views
155
OldSwimmer1650
OldSwimmer1650
D
  • Solved
Font Change in Cell A1 Changes Font in Cell B1
Replies
6
Views
153
DPXCV
D
E
  • Solved
Help speed up "change font color" routine
Replies
5
Views
198
EssKayKay
E
S
  • Solved
Changing cell location did not cause changing formula
Replies
1
Views
41
sebastianws
S
N
  • Question Question
Change Text in Cell by Value
Replies
6
Views
251
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top