Highlight Active Row and Column in Excel (Based on Cell Selection)

hello and welcome to the video by Trump Excel I am so miss Mansell and in this video I am going to show you how to highlight the active row and the active column based on the cell selection in your data set so here I have this data set and see what happens when I make a selection it would instantly highlight the row number the active row and the active column based on this cell selection and when I make a change in this election it would remove the previous highlight and it would highlight based on this selection now this is useful if you have a huge data set and you want to track what is the column which is the column that you have selected or which is the row that you have selected then you can use this method now you do not have to use the same color you can use different colors so in this case when I make a selection here you can see the column and the row gets highlighted in different colors now let's see how to create this so I have this data set here and I have not created that thing here that highlight here so I am going to show you how to do this that from scratch now to do that we are going to use three things we are going to use a formula we are going to use conditional formatting and a very easy VBA code now the formula in this case would be the cell formula and the cell formula gives you the information about the selected cell or the active cell in the selection now in this case it gives us stuff like the address column number color contents and all these things what we are going to use is the column number of the selected cell and the row number of the selected cell so in this case if I select column here and I hit enter and notice that this value call would be always be in double quotes and now when I hit enter it gives me 14 because 14 is the column number of this selected cell but if I come here and I change the selection I select this cell here and press f9 this gives me 17 I have to press f9 because this is a formula and formulas need to recalculate and therefore when I make a selection because there is no change in the work it is not getting recalculated so I need to press f9 to make this recalculate and this is where we are going to make this dynamic by using VBA so if I come here and I make a change in selection I select this one and I press f9 you can see the column number changes similarly if I use the road function the cell function with row argument you can see that it would give me the row number of the selected cell so if I select a cell here and press f9 it gives me 6 because the sixth row is selected so we are going to use this formula along with conditional formatting and before I show you how to use this formula in conditional formatting let's first create the formula that we are going to use so I'm going to use an odd formula and within this I am going to use two conditions the first condition would be if the cell column number is equal to the column number in the data set and if the cell the selected cells a row number is equal to the row number in the data set so this is my formula let me copy this formula because I'm going to paste this in conditional formatting and let me quickly explain this what this formula does is when I make a selection in this data set let's say I select this cell e5 then it is going to check whether the column number of cell e5 is equal to the column number in the data set now 'if I've the column number and row number both are 5 so it is going to check whether the column number is 5 which would only be true for the cells in column E and if the row number is equal to 5 which would only be true for the cells in row number 5 so the column number and the row number 5 are going to get highlighted by conditional formatting so I'm going to select this entire data set because I want to apply conditional formatting to it I would go to the Home tab here in conditional formatting I would go to new rule and I would say use a formula to determine which cells to format and here I would paste the formula now you can also type the formula directly here but because there is no it is easier to first do it in Excel and then copy pasted here now I would have to specify the color in which I want to highlight so I would click on format I would click the fill tab here and I would select the color and now when I click OK you can see it highlights the first column in the first row because that is the row and column number of the active sandwich is a 1 but if I make a change in the selection and press f9 you can see it changes the highlight it changes the row and column number that are getting highlighted similarly if I make a selection here and I press f9 it highlights these rows and column numbers based on the selection now I don't want to keep on pressing f9 every time there is a change in the selection instead I want this to be dynamic so to do that we are going to use VBA so I go to the Developer tab here and I would click on visual basic you can also use the keyboard shortcut alt f11 so hold the Alt key and press f11 and that could open the VB editor which is this now this is the VBA editor and here on the Left we have the project Explorer and we have two workbooks booked one which is this one and the other workbook which is open so you can see all the open workbooks here in this workbook because this is where my data set is I need to select the sheet on which I have the data so because this is sheet 1 I would double click on sheet1 and when I do that it opens the code window for sheet 1 now let me full screen this now in this case I would come here on this drop-down and I would select work sheet and here you would see it inserts worksheet underscore selection change event what this event is going to do is whenever there is a change of selection in the work sheet it is going to execute the code here so now all I have to do is enter one line of code which is application dot sorry calculate application dot calculate and see what happens I am going to minimize this now when I make a change in the selection it automatically highlights the active row and column based on that selection so I don't need to press f9 in the backend VBA is doing that automatically whenever there is a change in selection now if you want to highlight it in different colors you would have to apply to different conditional formatting rules so one would be for the column and one would be for the row I would leave that for you to decide and decipher how to do that another thing that I want to show you in this is this is working fine but there is one problem with this which is that if I copy this cell here and I try and paste it here it is not going to happen because as soon as I make the change in the selection the copy/paste part goes away so I would need to change my VBA code a little bit so I would again go to me usually basic and here I would need to make a very minor change in this coding I would say if application dot cut copy mode is equal to false then then run this else do not so now what I am doing I am doing in this cases I am checking whether the person or whether the user has made a copy or a cut so whether he has copied the cells or he has used ctrl X or he is cutting these cells if that is the case do not calculate or recalculate the sheet every time based on the selection change but if that is not true which means that if he has not copied it then simply recalculate it so now if I come here it's working fine and I can also copy paste it so if I copy it from here and I come here and paste it it is going to work so this is how you can use three things one is the formula which is the cell formula along with conditional formatting and VBA to quickly highlight the active row and the active column another very good thing about this is in case you have made any changes in the cell color so for example if I come here and I change the cell color to let's say yellow and I change the selection that cell color is not going to go away it will remain there so if I select this obviously the conditional formatting color takes over but when you change the selection that color that highlight is going to stay so it is not going to mess up your existing data set it is not going to mess up your existing formatting but it is going to help you dynamically highlight the active cell that active row and the active column based on the cell selection that's it in this video I hope you found this useful if you are watching this video on YouTube please subscribe to this channel to get new updates and awesome excel tips videos thank you and have a nice day

Channel: TrumpExcel

Views: 144,478

Rating: 4.910244 out of 5

Keywords: Highlight Active ROw, Highlight Active Column, Highlight Active Row in Excel, Highlight Active Columnn in Excel, Highlight Active Row and Column, VBA, CELL Function, Excel Conditional Formatting, Conditional Formatting, trump excel, trump excel videos

Id: 1sce8va1Yhg

Length: 9min 34sec (574 seconds)

Published: Thu Sep 20 2018