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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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. (y)
 
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

H
  • Solved
VBA worksheet change event
2
Replies
11
Views
238
hrayani
H
P
  • Solved
VBA to keep a running log of changes to specific cell
2 3
Replies
20
Views
798
Joe4
Joe4
R
  • Solved
Change the date
Replies
6
Views
183
mumps
mumps
M
  • Solved
Automatically add value to divide by
Replies
8
Views
144
Peter_SSs
Peter_SSs
David Kemp
  • Solved
Hide different columns depending on value in a cell
Replies
5
Views
111
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,807
Messages
6,121,679
Members
449,047
Latest member
notmrdurden

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