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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
180
OldSwimmer1650
OldSwimmer1650
T
  • Solved
Prevent changes to a single column
Replies
3
Views
123
tiredofit
T
D
  • Solved
Font Change in Cell A1 Changes Font in Cell B1
Replies
6
Views
161
DPXCV
D
E
  • Solved
Help speed up "change font color" routine
Replies
5
Views
251
EssKayKay
E
R
  • Solved
Show most recent value within column (furthest row down)
2
Replies
16
Views
298
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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