How To Unhighlight A Row In Excel Youtube

In this tutorial, you volition learn 3 different means to dynamically highlight the row and column of a selected prison cell in Excel.

When viewing a big worksheet for a long time, you may somewhen lose track of where your cursor is and which data you are looking at. To know exactly where you lot are at whatsoever moment, go Excel to automatically highlight the active row and column for you! Naturally, the highlighting should be dynamic and modify every fourth dimension y'all select some other cell. Essentially, this is what we are aiming to accomplish:

• Auto-highlight row and cavalcade of selected cell with VBA
• Highlight active column and row with provisional formatting
• Highlight selected row and column using conditional formatting and VBA
  • Highlight agile row
  • Shade active column
  • Highlight active row and column

Auto-highlight row and column of selected jail cell with VBA

This example shows how you lot tin highlight an active column and row programmatically with VBA. For this, we will exist using the SelectionChange event of the Worksheet object.

First, yous clear the background color of all cells on the sheet past setting the ColorIndex property to 0. And and then, yous highlight the entire row and column of the agile cell by setting their ColorIndex property to the index number for the desired color.

Private Sub Worksheet_SelectionChange(ByVal Target Every bit Range) 	If Target.Cells.Count > 1 And so Get out Sub 	Application.ScreenUpdating = Fake  	'Clear the color of all cells 	Cells.Interior.ColorIndex = 0 	With Target 		'Highlight row and column of the selected jail cell 		.EntireRow.Interior.ColorIndex = 38 		.EntireColumn.Interior.ColorIndex = 24 	End With  	Application.ScreenUpdating = True End Sub          

Customizing the code

If you'd like to customize the code for your needs, these small tips may come in handy:

• Our sample lawmaking uses two different colors demonstrated in the to a higher place gif - color index 38 for row and 24 for cavalcade. To change the highlight color, simply replace those with whatever ColorIndex codes of your choosing.
• To become the row and cavalcade colored in the aforementioned way, employ the same color index number for both.
• To only highlight the active row, remove or annotate out this line: .EntireColumn.Interior.ColorIndex = 24
• To only highlight the active column, remove or annotate out this line: .EntireRow.Interior.ColorIndex = 38

How to add together the code to your worksheet

To have the code silently executed in the background of a specific worksheet, you lot need to insert information technology in the code window belonging to that worksheet, not in the normal module. To have it done, carry out these steps:

1. In your workbook, printing Alt + F11 to get to the VBA editor.
2. In the Project Explorer on the left, you'll see a listing of all open workbooks and their worksheets. If yous don't see information technology, use the Ctrl + R shortcut to bring the Project Explorer window to view.
3. Find the target workbook. In its Microsoft Excel Objects folder, double-click on the canvas in which y'all want to utilize highlighting. In this instance, it's Sheet 1.
4. In the Code window on the right, paste the in a higher place code.
5. Save your file as Macro-Enabled Workbook (.xlsm).

Advantages: everything is done in the backend; no adjustments/customizations are needed on the user's side; works in all Excel versions.

Drawbacks: there are ii essential downsides that brand this technique extraneous under sure circumstances:

• The code clears background colors of all cells in the worksheet. If you accept any colored cells, do non use this solution considering your custom formatting will be lost.
• Executing this code blocks the undo functionality on the sheet, and yous won't be able to undo an erroneous activity by pressing Ctrl + Z.

Highlight active row and column without VBA

The all-time you can get to highlight the selected row and/or cavalcade without VBA is Excel's conditional formatting. To prepare information technology up, comport out these steps:

1. Select your dataset in which the highlighting should be done.
2. On the Home tab, in the Styles group, click New Rule.
3. In the New Formatting Rule dialog box, choose Use a formula to determine which cells to format.
4. In the Format values where this formula is true box, enter ane of these formulas:

   To highlight active row:

   =CELL("row")=ROW()

   To highlight agile column:

   =Jail cell("col")=Cavalcade()

   To highlight active row and column:

   =OR(CELL("row")=ROW(), CELL("col")= COLUMN())

   All the formulas brand use of the Jail cell role to return the row/column number of the selected cell.

5. Click the Format button, switch to the Fill tab, and select the colour yous like.
6. Click OK twice to close both dialog windows.

If y'all feel similar you need more than detailed instructions, delight encounter How to create formula-based conditional formatting rule.

For this example, we opted for the OR formula to shade both the column and row in the aforementioned color. That takes less piece of work and is suitable for almost cases.

Unfortunately, this solution is non as nice equally the VBA one because it requires recalculating the sail manually (by pressing the F9 key). By default, Excel recalculates a worksheet but subsequently inbound new data or editing the existing one, but not when the selection changes. And so, y'all select another jail cell - nothing happens. Press F9 - the sail is refreshed, the formula is recalculated, and the highlighting is updated.

To become the worksheet recalculated automatically whenever the SelectionChange effect occurs, you can place this unproblematic VBA code in the code module of your target sheet every bit explained in the previous instance:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 	Target.Calculate Stop Sub          

The code forces the selected range/prison cell to recalculate, which in plough forces the Prison cell function to update and the conditional formatting to reflect the modify.

Advantages: unlike the previous method, this one does not impact the existing formatting you have applied manually.

Drawbacks: may worsen Excel's performance.

• For the conditional formatting to piece of work, you lot need to force Excel to recalculate the formula on every selection alter (either manually with the F9 key or automatically with VBA). Forced recalculations may slow downward your Excel. Since our lawmaking recalculates the selection rather than an entire sheet, a negative effect will near probable be noticeable only on really large and complex workbooks.
• Since the CELL function is available in Excel 2007 and higher, the method won't work in earlier versions.

Highlight selected row and column using provisional formatting and VBA

In case the previous method slows down your workbook considerably, you can approach the job differently - instead of recalculating a worksheet on every user movement, get the active row/column number with the help of VBA, and and then serve that number to the ROW() or Cavalcade() office past using conditional formatting formulas.

To attain this, here are the steps you need to follow:

1. Add together a new blank canvass to your workbook and name information technology Helper Canvas. The merely purpose of this sheet is to store two numbers representing the row and column containing a selected cell, so y'all tin can safely hide the sheet at a later betoken.
2. Insert the beneath VBA in the lawmaking window of the worksheet where you wish to implement highlighting. For the detailed instructions, please refer to our first example.

                   Individual Sub Worksheet_SelectionChange(ByVal Target As Range) 				Application.ScreenUpdating = Imitation  				Worksheets("Helper Canvass").Cells(2, i) = Target.Row 				Worksheets("Helper Sheet").Cells(ii, 2) = Target.Column  				Awarding.ScreenUpdating = True 			Cease Sub              

   The in a higher place code places the coordinates of the active row and column to the sheet named "Helper Sheet". If you named your sheet differently in stride 1, change the worksheet name in the code appropriately. The row number is written to A2 and the cavalcade number to B2.

3. In your target worksheet, select the entire dataset, and create a conditional formatting rule with the below formulas. The step-by-step guidance is provided in the above example.

And now, let's embrace the three chief utilise cases in item.

How to highlight active row

To highlight the row where your cursor is placed at the moment, set upwardly a conditional formatting rule with this formula:

=ROW()='Helper Sheet'!$A$2

As the result, the user can conspicuously see which row is currently selected:

How to highlight active column

To highlight the selected column, feed the cavalcade number to the Column part using this formula:

=Column()='Helper Canvass'!$B$ii

Now, a highlighted column lets you comfortably and effortlessly read vertical data focusing entirely on it.

How to highlight active row and cavalcade

To become both the selected row and column automatically shaded in the same color, combine the ROW() and Column() functions into one formula:

=OR(ROW()='Helper Sheet'!$A$ii, COLUMN()='Helper Sheet'!$B$2)

The relevant data is immediately brought into focus, so you tin can avoid misreading it.

Advantages: optimized performance; works in all Excel versions

Drawbacks: the longest setup

That's how to highlight the column and row of a selected cell in Excel. I thanks for reading and wait frontwards to seeing y'all on our blog adjacent week!

Practice workbook for download

Highlighting agile row and column (.xlsm file)

You may besides exist interested in

Source: https://www.ablebits.com/office-addins-blog/highlight-active-row-column-excel/

Posted by: strongpressessidow1985.blogspot.com

Share this post