You will be redirected back to the Conditional Formatting Rules Manager. Click on the OK command button when done. While this is something that is not there as an inbuilt functionality in Excel, it can be done using the filter method that uses a helper column or the VBA method.Īnd in case you have a small data set, it’s best just to select every alternate row manually. Set the color for highlighting the rows that this formula applies to by clicking on the Format button then selecting the color in the Fill.
In this article, I showed you different three different ways you can use to select every other row in Excel. One such add-in you can consider getting is Dose Excel, which allows you to specify the interval and the number of rows that can be selected.įor example, you can specify that you need to select one row after every other row (or one row after every two rows). If selecting every other row is something you need to do quite often, you can also consider investing in third-party Excel add-ins that can make the process very easy for you.
EXCEL HIGHLIGHT EVERY OTHER ROW AUTOMATIUCALLY CODE
Once you have added this code in the workbook where you want to use it or in the Personal Macro Workbook, you can also add an icon to run the code with a single click from the Quick Access Toolbar.Īdd-in to Select Every Other Row in Excel.In case you want to use this code on all your Excel workbooks, you should save this in your Personal Macro Workbook This code would only work in the workbook where it has been added.When you add this VBA code to your workbook, you need to save your workbook as a macro-enabled file (with a.In case you want to select every other row starting from row number 2, you can modify the code as shown below: 'Code developed by Sumit Bansal from Ī few things to know when using this macro code: This will open the VB editor, where we are going to copy and paste the above code. Click the Developer tab and then click on the Visual Basic icon.Select the dataset where you want to select every alternate row.Open the Excel workbook where you want to use this VBA code.it then uses a simple For Next loop to go through all the rows and only select every second row, starting from the third row.īelow are the steps to use this VBA code in Excel: The above VBA code starts from the third row in our dataset. Set RowSelect = Union(RowSelect, MyRange.Rows(i)) If selecting every other row is something you need to do quite often, you should definitely consider trying out the VBA method.īelow is the VBA code that would select every row in your data set that you selected. While the above two methods work well, they are a little time-consuming. Using VBA to Select Every Other Row in Excel So anything you do to this data set would not impact the cells that are not visible.įor example, if you highlight all these visible cells and then you remove the filter, Only the alternate rows would have been highlighted. Pick a table style that has alternate row shading. Here's how: Select the range of cells that you want to format. When you select the cells after the data set has been filtered, it only selects the cells that are visible. By using this method, alternate row or column shading is automatically applied when you add rows and columns.
0 kommentar(er)
