Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



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
=================================
Comments:
Pattern=-4142 puts the gridlines around the active cell
RGB(255,255,255)=White
RGB(255,255,160)=Light Yellow


Check out our Excel VBA Resources

Re: Globally Highlight Current Row in Color

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.

Damon




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.