in my previous video on excel VBA code I’ve shown you how to run a macro when you select a
particular cell one of my viewers has asked me in the comments that cell a1 be equal to a value from
range b1 to b10 if any value in this range is selected if any other cell is selected outside
this range the value of a1 should be equal to b1 so that's what we're gonna do first as always
go to the developer tab then go to the visual basics and here if you select sheet1 we can start
coding the first line of code always write option explicit enter, enter and then we need to create
our first sub which is private sub worksheet underscore selection change brackets by value
target as range enter you can write in a lower case and visual basics would automatically
correct it for you then the code should be if selection dot count is equals to one then
tab if not intersect bracket target range b1 to b10 close the range and close the bracket and
again close the bracket is nothing then we need to call under the sub for that press tab and then
type call and now we need to give the sub a name and i will call this show b 1 2 b 10 enter back
so what does this means this means if any cell in the range of b 1 to b 10 is selected then we will
call another sub and if anything else is selected we want another result so we need to type else
enter forward tab call show active enter back and we need to close the if statement because
we got two nested if statements so we need to go back and close the other if statement as well
so this is the main sub and now we need to write our other two subs the first one would be private
again always write private sub show b 1 to b 10 enter when you write range it can be one cell
it can be a range of cells so if you type range and open the bracket and then type a1 and we want
its value to be equal to the active cell so we need to write just active cell dot value and
that's it it's a very simple sub and then we need to write on this sub private sub show active and
this one we want again the range a1 value would be equal to but this time we want it to be equal to
cell b1 and then value and that's it the code is as simple as this what does this mean let me
explain very quickly through this so when anything selected in that sheet which is sheet 1 this sub
would get activated and if a cell in the range of b1 to b10 is selected then we will call this sub
and what does this sub does it gives a value of the active cell which we already know is a
cell value of in the range of b1 to b10 and assign that to cell a1 and if any other cell is selected
then we call this function and this function says that the value of cell a1 should be equal to the
value of cell b1 so click on save and now we go to our excel sheet and if i would select any value on
this range okay it gives me an error what is says that the spelling of intersect is not correct so i
corrected the spelling and then run again so if i select cell b3 so b3 is selected anything i select
it would automatically assign that value to a1 and if i select anything outside this range it would
assign a1 to b1 i hope this video was useful if it was please don't forget to watch my other videos
on my left and to subscribe to support my channel i hope to see you in my next video bye