How to highlight entire row when clicking a cell in Excel worksheet?
If you have a large worksheet with multiple columns, it can be difficult to track data across a row. To make it easier, you can highlight the entire row of the active cell. This helps you quickly identify all data in that row when clicking different cells. In this tutorial, I will discuss several methods to achieve this.
Highlight entire row when clicking a cell with VBA code
Highlight entire row/column when clicking a cell with Kutools for Excel
Highlight entire row when clicking a cell with VBA code
The following VBA code will allow you to dynamically highlight the entire row of the selected cell:
1. Activate the worksheet where you want to highlight rows. Right-click the sheet tab and choose View Code from the right-clicking menu. See screenshot:
2. In the opened Microsoft Visual Basic for Applications window, copy and paste the following code into the blank module:
VBA code: Highlight whole row when scrolling
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice
Cells.Interior.ColorIndex = 0
Target.EntireRow.Interior.ColorIndex = 8
End Sub
3. Save and close the code. Now, when you click a cell, the entire row of that cell will be highlighted, as shown in the screenshots below:
 |
 |
 |
Notes:
1. You can change the highlight color by modifying this line in the script "Target.EntireRow.Interior.ColorIndex = 8". Change the number 8 to another color index.
2. To disable this feature, simply delete the code from the Microsoft Visual Basic for Applications window.
3. This code can also highlight rows of multiple selected cells, as shown below:
4. This code is only applied to the current worksheet.
5. If there are some colored cells in your worksheet, the cell color will be lost when you apply this code.
Highlight entire row/column when clicking a cell with Kutools for Excel
With Kutools for Excel, the Reading Layout feature allows you to easily highlight the entire row, entire column, or both when you click a cell.
After installing Kutools for Excel, follow these steps:
1. Select a cell or a range of cells where you want to highlight rows.
2. On the Kutools tab, click the Reading Layout drop-down button, and choose Reading Layout Settings. See screenshot:
3. In the Reading Layout Settings dialog box, select your preferred highlight style under the Shapes section. You can also customize the style, color, and transparency. See the screenshot below:
Tips: To highlight both the row and column of the active cell, choose Criss-cross under the Shapes section. To highlight the entire column, choose Vertical line.
4. After finishing the settings in the dialog box, please click Ok, and now, when you click a cell, the whole row will be highlighted at once. The highlight will move dynamically as you select different cells.
Notes:
1. To disable the feature, uncheck the Reading Layout.
2. This Reading Layout feature is applied to all of the worksheets within your workbook.
3. This Reading Layout feature will be disabled when you launch the workbook next time.
4. Unlike the VBA method, Kutools preserves any pre-existing cell colors.
Click to know more details about this Reading Layout.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Demo: Highlight entire row/column when clicking a cell with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in