Formula it's a very simple formula and all we're going to do is we're going to write in equals and then we're going to select the cell in which we're using to record the row so in this case it's a four okay so a four and then we're going to equals and then row and then open and close parentheses like that okay and now we're going to set the format what is the format that we want for this particular row so we're going to click format and then we're going to click fill okay fill okay what.
Do we want we want to we want to fill this row with some different colors so we're going to click on fill effects here and in this case I'm going to use a dark blue theme so we'll click we're going to click two colors and then we're going to click basically we're going to use a fade out from a dark blue to just a little bit lighter so we'll click here and then also want to change the font color of that selected row so I'm going to click spawned okay we're going to make it bold and then.
We're going to select the color and we're going to select white upper left-hand corner okay so we're going we're going to select a white font on a dark background okay and you can't see it here but it's a white font on the preview then we're going to click OK and so basically here is the preview of what that selected row is going to look like okay and when we click OK and we'll take a look and see now it's select a row and if we go back into the conditional formatting we can see the rule that we.
Just created okay so we'll go in just we're going to manage rules and here we see the conditional formula we just created and this applies to the entire range because we selected that range it's automatically automatically set we can also re select the range by choosing a different range okay just simply by selecting the range okay but we'll keep it from me five 2k14 and that is the range of the table we can set this range you can set it you can set the rows to go all the way down.
Very simple and I'm going to walk you through it there's two ways to get into the VBA screen and one way is to Alt f11 alt f11 is a shortcut and that will get you to the VBA screen here the other way is if you showing a Developer tab to click Visual Basic like that and if you do not have a Developer tab which most of you don't you simply go into properties options here options and then click on the customize ribbon and then just select the Developer tab right here so by selecting that.
It will show up here ok and so once we do that we once we launch the Visual Basic what we want to do then is basically create some color when we select a particular cell and so what we're going to do is we're going to click first we're going to make sure that we're on the right sheet and we're currently working in sheet 1 so we want to stay in sheet 1 okay whatever sheet you're working there's just double click that to make sure you're on the right sheet and what we want to do is we.
Want to click worksheet here from the options here worksheet and then the idea is what what action do you want to run this code and our idea is going to be a selection change so you want to look for selection change which should already be because basically we want some code to run when we select a cell ok and so we're going to keep it a selection change and that means you have to write some code in between this private sub worksheet selection change and the anthem so we're going.
To write code between none basically the code we're going to write is basically it says when we select a cell within the range of this table we want whatever row we're selecting we want the row to appear here okay so that is the idea so we're going to write some code right and basically is this if not intersect and then parentheses target and you don't have to remember this I'll make sure there's a download available so you can copy and paste it so please don't worry about it and then.
What wonders we want to select the range and what is the range the range is basically e5 through K 14 okay e5 through K 14 okay and that's the range and then close the parentheses twice let me open it ok is nothing then and what do we want to do ok and you notice the end if popped up automatically it will not on yours because I've got some special so just make sure you write and if write and then everything in between is here and so basically what do we want to happen well we want the rotor.
Posts Related:
- Noob vs Pro Excel LOOKUP Values with two spreadsheets
- 3 Easy Ways to Create Your Power BI Calendar Table / Date Table
- Excel 2019 VBA Intermediate Tutorial (Part 2 of 4)
Right but just so we're making sure let's just put some code in here message box test just so we know
That it's working right ok so when we go back now we go back and we select test pop up comes up great ok when we select outside thing happens when we select inside test comes up okay so we know that code is working great but we don't want a message like test we want this row to change a four to change so we're going to select range okay a four dot value equals target dot row okay.So basically the target the role this will pull it the actual row of whatever we want inside this cell here so let's go ahead and test that out now you see how the road changes and because because the rows changing because we have conditional formatting set up it changes automatically it's very simple okay and I'll make sure to include a link to this workbook so that you can copy and pasted code you don't need to remember okay and now one other option is and you'll see here.
We're selecting every row but what if we only want to select rows with actual values and if it's blank we don't want to select well that's an easy option too and I'll walk you through that all we have to do is make it slight change within the conditional formatting and I'll walk you through that here so we pull up our managing rules and pull up that conditional formatting and we edit that rule and basically what we want to do is we want to say to cell hey only if there's values in.
A specific column do we want the highlighting to show up so we just have to start here and we put in an okay because there's going to be more than one option now and and what we do is we'll select the first because most tables will have your customer name whichever field you feel is required or which we'll put in there so in this case we're going to use customer name e5 okay but we want to use this for every row of the table not just Row 5 so we don't want that fixed.
We're going to take the dollar sign before five out okay so if e5 does not equal nothing then we want to run the code so that's all we have to do it we don't want to close those parentheses okay so basically there's your condition that we've added so we want to make sure that a the column is fixed everything in yi and every row within that table it does not equal does not equal nothing then we wanted to run better will change okay so we click okay okay and the rain stays the.
Same so now we'll just apply that and click OK now when we select nothing now when we select a row with nothing in column E no highlighting is done except only rows where there's a value in column E and that's pretty much it that's how you select it and oftentimes you don't want your you know you may not want to see this so you can just hide the column okay so you keep it simple we can hide that that way people don't see it if you don't want it to be really so usually put it.
