Highlighting columns or rows in Excel when selected
Highlighting columns or rows in Excel when selected: To make the contents or important data in an Excel spreadsheet clearer, we have the option to shade alternate rows or cells in Excel, depending on the position where we want to emphasize the content. Then, the alternating colors will display for all the data objects in the table. If you only want to have the highlight when you click directly, you can use VBA code. With this method, the table data will be highlighted by row or column when compared, just by clicking the mouse when needed. Please follow the article below to learn how to use VBA to highlight columns and rows in Excel.
1. Highlighting Excel columns
Step 1: To highlight the row by shading that row when you click any cell, first, press the Alt + F11 shortcut keys to open the Microsoft Visual Basic for Applications window.
Step 2: Now, the VBA window will appear. Double-click on the sheet you want to operate in the sheet list on the left. Then copy the following code segment:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Cells.Interior.ColorIndex = 0
ActiveCell.EntireColumn.Interior.ColorIndex = 8
Application.CutCopyMode = True
End Sub
Highlighting columns or rows in Excel when selected
Step 3: Then press Alt + Q to close the VBA window. After that, return to the Excel worksheet. Then, just click any cell and the column containing that cell will be highlighted.
Highlighting columns or rows in Excel when selected
2. Highlighting Excel rows
Step 1: To highlight the row by shading that row when you click any cell, first, press the Alt + F11 shortcut keys to open the Microsoft Visual Basic for Applications window.
Step 2: Now, the VBA window will appear. Double-click on the sheet you want to operate in the sheet list on the left. Then copy the following code segment:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Cells.Interior.ColorIndex = 0
ActiveCell.EntireRow.Interior.ColorIndex = 8
Application.CutCopyMode = True
End Sub
Highlighting columns or rows in Excel when selected
Step 3: Then press Alt + Q to close the VBA window. After that, return to the Excel worksheet. Then, just click any cell and the row containing that cell will be highlighted.
Highlighting columns or rows in Excel when selected
3. Highlighting both Excel columns and rows
Step 1: To highlight both columns and rows by shading that row when you click any cell, first, press the Alt + F11 shortcut keys to open the Microsoft Visual Basic for Applications window.
Step 2: Now, the VBA window will appear. Double-click on the sheet you want to operate in the sheet list on the left. Then copy the following code segment:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Cells.Interior.ColorIndex = 0
ActiveCell.EntireRow.Interior.ColorIndex = 8
ActiveCell.EntireColumn.Interior.ColorIndex = 8
Application.CutCopyMode = True
End Sub
Highlighting columns or rows in Excel when selected
Step 3: Then press Alt + Q to close the VBA window. After that, return to the Excel worksheet. Then, just click any cell and both the row and column containing that cell will be highlighted.
Highlighting columns or rows in Excel when selected
In conclusion, highlighting columns or rows in Excel can be a helpful way to emphasize important data in a spreadsheet. By following the steps above, you can easily use VBA code to highlight either columns, rows, or both, depending on your needs. This feature can save you time and effort when working with large datasets in Excel, making it easier to identify and analyze important information. So, try it out and see how it can benefit your work with Excel.
