- #Excel highlight every other row automatiucally how to
- #Excel highlight every other row automatiucally code
That takes less work and is suitable for most cases.
#Excel highlight every other row automatiucally 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 highlight every other row automatiucally 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Īpplication.ScreenUpdating = True End Sub Customizing the code Private Sub Worksheet_SelectionChange( ByVal Target As Range)Īpplication.ScreenUpdating = False 'Clear the color of all cells
0 kommentar(er)
