Hi, this is Wayne again with a topic “Create an Excel Macro that Highlights Rows”.
In this excel video, i’m going to show you how to create an excel macro that will make it so that, whatever range you have selected, every other row in that range will be highlighted in a color of your choice, and this video is one in a series On excel macros, please watch the entire series. As always, when we’re creating macros step, one is always to make sure that you have the developer tab on your ribbon. If you don’t, you can go here. Click this quick access, toolbar button! You can go to more commands, switch to customize the ribbon and make sure that that’s checked here and then click. Ok. The next step is to go to the visual basic button and click, and you can see that visual basic for applications recognizes my spreadsheet.
It’S a contacts list and by the way, if you want a copy of this contacts list to practice with look in the description below the video and you’ll find a link to that, and what i need to do next is decide. Do i want this macro to reside just on sheet 1 or anywhere in the workbook? In this case it doesn’t really matter. I guess i’ll choose this workbook, so i’ll double click on this workbook and i get a white workspace in which to put the code that will make this work. That will make every other row highlighted in a color and in this case i’m just going to paste in the code into the space rather than typing each word one at a time while you’re watching now.
This is a programming language, but please don’t be intimidated by that. I like to think of code like this code as being kind of like a recipe or a magic spell as long as you know, the recipe you’re able to create the food that you want to create, but now that that code is in place, i can simply Go up here to the upper right and x out of microsoft, visual basic for applications, which is built in to modern versions of excel so i’ll x. Out of that – and it looks like nothing happened, but if i go to the developer tab in the code group, there’s macros and i can click and i can see that the macro does exist and i could click run to execute the macro.
But instead i would like to create a button that i can use whenever i need to, and that makes it easier to access the macro. So i’m going to shorten up some of these columns just by double clicking between the two columns, it auto sizes, and i think i’ll also zoom out a little bit. Because i want my button to be here and there’s a couple of different ways. You could create buttons, you could go to the developer, tab, insert button and put that in and that’s a good way to do it or you could insert an illustration, let’s say a shape, i’m going to go with this rectangular shape here, click and drag on the Screen there’s my button: i can choose the style for my button that i want and then double click on it i’ll call this highlight rows and i can center the text. If i want to. I can also center it vertically.
If i want, i can make it bigger, i could make the text bigger whatever i want to do to customize this button and make it look the way i want it to look so there’s my button. You of course could design your button. However, you want to, but at this point all i have to do is right: click on the button and choose assign macro. There’S the macro that i already created. I select it click, ok and then i can click away from the button. And now it’s ready to be used, but watch what happens if i click the button now, if i click it, nothing happened.
Why? Well, the reason! Why is because the purpose of this macro is to examine whatever is selected and then make every other row highlighted. So i need to highlight a range you can’t just have one cell selected now that the range is highlighted. I can click highlight rows and you can see the results now that blue is a little intense.
Maybe it’s too dark. So i could go here to the developer: tab go to macros with the macro selected. I can go to edit and i get back into microsoft, visual basic for applications and i can delete out blue and replace it with another color, maybe yellow x out of vba.
Now, this time, just for contrast, i’m going to click and drag and select only part of the range i’ll click highlight rows and you can see that works a little better, so feel free to experiment with the different colors. Let’S try this one highlight rows that one works pretty well now, because i’ve created a macro or used a macro in this spreadsheet. When i go to save this spreadsheet notice, what happens? I get a warning saying that the following features: basically, the vba project cannot be saved in a macro free workbook.
So i need to convert this workbook into a macro. Enabled workbook it’s easy to. Do you just click? No, you get this pop-up and then here where it says, save as type just click to change it to an excel macro, enabled workbook click save notice that the extension for this file has changed xlsm and now, when i close the spreadsheet and then open it up Again notice that i get a security warning about macros, i have to enable content in order to be able to use the macro button thanks for watching this video.
I hope you found it to be helpful. If you did please like follow and subscribe and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do that through my patreon account or by buying channel merchandise, and you can learn more about those opportunities by looking below the video speaking of patreon. I want to give a quick shout out to my five dollar patreon supporters.
Thank you. So much for all you do to support my channel. .