#Excel shortcut for highlighting row how to
#Excel shortcut for highlighting row code

Unfortunately, this solution is not as nice as the VBA one because it requires recalculating the sheet manually (by pressing the F9 key). That takes less work and is suitable for most cases.

#Excel shortcut for highlighting row how to

If you feel like you need more detailed instructions, please see How to create formula-based conditional formatting rule.įor this example, we opted for the OR formula to shade both the column and row in the same color. Click OK twice to close both dialog windows.Click the Format button, switch to the Fill tab, and select the color you like.=OR(CELL('row')=ROW(), CELL('col')= COLUMN())Īll the formulas make use of the CELL function to return the row/column number of the selected cell. In the Format values where this formula is true box, enter one of these formulas:.In the New Formatting Rule dialog box, choose Use a formula to determine which cells to format.On the Home tab, in the Styles group, click New Rule.Select your dataset in which the highlighting should be done.The best you can get to highlight the selected row and/or column without VBA is Excel's conditional formatting. Highlight active row and column without VBA

#Excel shortcut for highlighting row code

Executing this code blocks the undo functionality on the sheet, and you won't be able to undo an erroneous action by pressing Ctrl + Z.If you have any colored cells, do not use this solution because your custom formatting will be lost. The code clears background colors of all cells in the worksheet.Save your file as Macro-Enabled Workbook (.xlsm).Īdvantages: everything is done in the backend no adjustments/customizations are needed on the user's side works in all Excel versions.ĭrawbacks: there are two essential downsides that make this technique inapplicable under certain circumstances:.In the Code window on the right, paste the above code.In its Microsoft Excel Objects folder, double-click on the sheet in which you want to apply highlighting. If you don't see it, use the Ctrl + R shortcut to bring the Project Explorer window to view. In the Project Explorer on the left, you'll see a list of all open workbooks and their worksheets.In your workbook, press Alt + F11 to get to the VBA editor.To have the code silently executed in the background of a specific worksheet, you need to insert it in the code window belonging to that worksheet, not in the normal module. To only highlight the active column, remove or comment out this line.To only highlight the active row, remove or comment out this line.To get the row and column colored in the same way, use the same color index number for both.To change the highlight color, just replace those with any ColorIndex codes of your choosing. Our sample code uses two different colors demonstrated in the above gif - color index 38 for row and 24 for column.If you'd like to customize the code for your needs, these small tips may come in handy: 'Highlight row and column of the selected cell Private Sub Worksheet_SelectionChange(ByVal Target As Range) And then, you highlight the entire row and column of the active cell by setting their ColorIndex property to the index number for the desired color. For this, we will be using the SelectionChange event of the Worksheet object.įirst, you clear the background color of all cells on the sheet by setting the ColorIndex property to 0. This example shows how you can highlight an active column and row programmatically with VBA. Highlight selected row and column using conditional formatting and VBAĪuto-highlight row and column of selected cell with VBA.Highlight active column and row with conditional formatting.Auto-highlight row and column of selected cell with VBA.Essentially, this is what we are aiming to achieve: To know exactly where you are at any moment, get Excel to automatically highlight the active row and column for you! Naturally, the highlighting should be dynamic and change every time you select another cell. When viewing a large worksheet for a long time, you may eventually lose track of where your cursor is and which data you are looking at. In this tutorial, you will learn 3 different ways to dynamically highlight the row and column of a selected cell in Excel.