Highlight Current Row in Excel (VBA)

**Rick Rothstein** · 04-19-2012, 09:26 PM

While this is different in theory from what you posted, it is similar in idea and I thought readers of this thread might find it interesting as well. This event code will highlight the row and column whose intersection is the active cell or cells. Note, because the code is changing the interior color of cells, that assumption is that no cells on the worksheet have their cells colored (if they do, those colors will be lost the first time a selection is made on the worksheet).

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Cells.Interior.ColorIndex = xlColorIndexNone
  Union(Target.EntireRow, Target.EntireColumn).Interior.ColorIndex = 6
End Sub

**Rajan_Verma** · 04-20-2012, 10:40 AM

Faster one :

Code:

Public strAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If strAddress <> "" Then
       Union(Me.Range(strAddress).EntireColumn, Me.Range(strAddress).EntireRow).Interior.Color = xlColorIndexNone
    End If
       Union(Target.EntireColumn, Target.EntireRow).Interior.Color = 65535
       strAddress = Target.Address
End Sub

**Rick Rothstein** · 04-20-2012, 11:28 AM

Originally Posted by Rajan_Verma

Faster one :

Code:

Public strAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If strAddress <> "" Then
       Union(Me.Range(strAddress).EntireColumn, Me.Range(strAddress).EntireRow).Interior.Color = xlColorIndexNone
    End If
       Union(Target.EntireColumn, Target.EntireRow).Interior.Color = 65535
       strAddress = Target.Address
End Sub

I am not completely convinced that your code really is any faster than what I posted. I threw a couple of Debug.Print Timer statements in each of our routines and selected around... both seemed to report 0 or 1 clock ticks worth of time (mostly 0) pretty much with the same frequency. I believe Excel must have some sort of optimized code underneath for processing all the cells on the sheet in a single reference... probably restricting itself, in the case of Interior Colors or ColorIndexess, to only cells with color in them.

**Rick Rothstein** · 04-20-2012, 01:48 PM

Originally Posted by Rajan_Verma

Faster one :

Code:

Public strAddress As String
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If strAddress <> "" Then
       Union(Me.Range(strAddress).EntireColumn, Me.Range(strAddress).EntireRow).Interior.Color = xlColorIndexNone
    End If
       Union(Target.EntireColumn, Target.EntireRow).Interior.Color = 65535
       strAddress = Target.Address
End Sub

By the way, had your approach been measurably faster, then I might have written it this way instead...

Code:

Public PreviousTarget As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not PreviousTarget Is Nothing Then PreviousTarget.Interior.ColorIndex = xlColorIndexNone
  Set PreviousTarget = Union(Target.EntireRow, Target.EntireColumn)
  PreviousTarget.Interior.ColorIndex = 6
End Sub

**salman8200** · 12-08-2012, 07:29 PM

How About Only Select the Rows?????Plz tell me, I dont know much about VBA. thanks

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Cells.Interior.ColorIndex = xlColorIndexNone
  Union(Target.EntireRow, Target.EntireColumn).Interior.ColorIndex = 6
End Sub

[/QUOTE]

**Rick Rothstein** · 12-08-2012, 07:48 PM

Originally Posted by salman8200

How About Only Select the Rows?????Plz tell me, I dont know much about VBA. thanks

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Cells.Interior.ColorIndex = xlColorIndexNone
  Union(Target.EntireRow, Target.EntireColumn).Interior.ColorIndex = 6
End Sub

Give this a try...

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Cells.Interior.ColorIndex = xlColorIndexNone
  Target.EntireRow.Interior.ColorIndex = 6
End Sub