Background
When working in Excel, you might lose track of which row you were looking at because there are too many columns. This is common with horizontal scrolling or long tables where it is easy to lose sight of the row containing the selected cell.
Using VBA code to automatically color the currently selected row instantly improves visibility. Here is the specific method to achieve this.
Using the “Worksheet_SelectionChange” Event
We will use the Worksheet_SelectionChange event. Below is the code that fills the selected row in yellow and restores the color of the previous row. You can copy and paste it as is.
Private PreviousRow As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Restore the color of the previously selected row
If Not PreviousRow Is Nothing Then
PreviousRow.Interior.ColorIndex = xlNone
End If
' Color the currently selected row yellow
Target.EntireRow.Interior.Color = RGB(255, 255, 0)
' Remember the current row
Set PreviousRow = Target.EntireRow
End Sub
Implementation Steps
- Open Excel and press Alt + F11 to launch the VBA editor.
- Double-click the target worksheet (e.g., “Sheet1”) in the Project Explorer on the left.
- Paste the code above into the code window for that sheet.
- Save and return to Excel; the changes will be reflected immediately.
Note: This code keeps the selected row highlighted in yellow. When you select a different row, the previous row returns to its original color.
Customization Examples (Changing Colors)
You can use different colors by changing the RGB(255, 255, 0) part of the code:
- Blue:
RGB(173, 216, 230) - Green:
RGB(144, 238, 144) - Gray:
RGB(211, 211, 211)
Please adjust the colors to suit your preference.
Summary
By implementing automatic row coloring in Excel using VBA, you can prevent losing your place while working.
This technique is especially effective for wide data tables or analysis sheets. You can achieve effective improvements with very simple code, so please try incorporating it into your workflow.
