MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Globally Highlight Current Row in Color

Posted by Mark P. Medina on September 04, 2001 12:28 PM

I have the VB code to do this but don't want to have to paste it in every single worksheet I might be working on. Is there a way to implement this globally ?

The code I have is this:

(general) (declaration)
Dim x As Long

(Worksheet) (SelectionChange)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Set the row containing the active cell to light yellow w/BOLD font
ActiveCell.EntireRow.Interior.Color = RGB(255, 255, 160)
ActiveCell.EntireRow.Font.Bold = True
' Check for first execution of the macro and set row
' value if it is:
If x = Empty Then
x = ActiveCell.Row
' Set previous row property back
ElseIf Not x = ActiveCell.Row Then
Rows(x).EntireRow.Interior.Color = RGB(255, 255, 255)
If x > 1 Then Rows(x).EntireRow.Font.Bold = False
Rows(x).EntireRow.Interior.Pattern = -4142
End If
' Capture new row value for comparison against next selection.
x = ActiveCell.Row
End Sub
Pattern=-4142 puts the gridlines around the active cell
RGB(255,255,160)=Light Yellow

Posted by Damon Ostrander on September 05, 2001 11:05 AM

Hi Mark,

To make this work on any sheet, simply change it from a worksheet-level event to a workbook-level event. To do this, paste the code into the Workbook_SheetSelectionChange event in the ThisWorkbook event code pane. To get to this pane quickly from Excel, right-click on the Excel icon at the left edge of the worksheet menu bar and select View Code.

The Workbook_SheetSelectionChange has two arguments, the first being the sheet that is selected, but you will not need to use this since in your problem the worksheet is always the active sheet.

Happy computing.