Highlight Current Row in Excel (VBA)

[Admin]

Hi All,

Here is a way to highlight the current row.

This code goes in worksheet module. Right click Tab Name > View code and paste the code there on the VBE window.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'// Developed by Kris @ ExcelFox.com

Dim x, nmRow As Name

'if A1 holds '0', the macro won't fire
If Me.Range("A1") = 0 Then Exit Sub 'adjust the flag cell

On Error Resume Next
Set nmRow = ThisWorkbook.Names("tRow")
On Error GoTo 0

Const HighlightColor As Long = 6750207 'Adjust the highlight color

If nmRow Is Nothing Then
Set nmRow = ThisWorkbook.Names.Add("tRow", Target.Row & "|" & Target.EntireRow.Interior.Color, 0)
Target.EntireRow.Interior.Color = HighlightColor
Else
x = Split(Evaluate("tRow"), "|")
Me.Rows(CLng(x(0))).Interior.Color = IIf(CLng(x(1)) = 16777215, -4142, CLng(x(1)))
nmRow.RefersTo = Target.Row & "|" & Target.EntireRow.Interior.Color
Target.EntireRow.Interior.Color = HighlightColor
End If

End Sub

Note: This code ensures that you won't lose your row color.

Enjoy !

[Nishant Choudhary]

great :)

[Rick Rothstein]

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).


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

[Rajan_Verma]

Faster one :



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]

Faster one :



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]

Faster one :


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...


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]

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


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]

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

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...

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

[salman8200]

Thanks

[hvg88]

Thanks

Thanks to all experts,
I want to know what for all worksheet to highlight current raw?

[Rick Rothstein]

Thanks to all experts,
I want to know what for all worksheet to highlight current raw?
Put this code in the workbook's code module (do not put it in any of the worksheet modules)...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Cells.Interior.ColorIndex = xlColorIndexNone
Target.EntireRow.Interior.ColorIndex = 6
End Sub

If you are not sure where the workbook module is, look over at the Project Explorer window within the VBA editor (CTRL+R if you do not see it) and then double-click the entry labeled ThisWorkbook... that will open the workbook code module... copy paste the code in it. Remember though, with this code you cannot have any other manually colored cells as the code will remove their colors when executing.

[luna]

is there a code that does the same thing (highlight active row) with out removing original highlighted color after cell is no longer active?




Put this code in the workbook's code module (do not put it in any of the worksheet modules)...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Cells.Interior.ColorIndex = xlColorIndexNone
Target.EntireRow.Interior.ColorIndex = 6
End Sub

If you are not sure where the workbook module is, look over at the Project Explorer window within the VBA editor (CTRL+R if you do not see it) and then double-click the entry labeled ThisWorkbook... that will open the workbook code module... copy paste the code in it. Remember though, with this code you cannot have any other manually colored cells as the code will remove their colors when executing.

[luna]

i found this code, it doesnt change the original highlighted color

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Rows(Target.Row).Select ' highlite entire row
Target.Activate ' select the cell

End Sub

[Rick Rothstein]

i found this code, it doesnt change the original highlighted color

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Rows(Target.Row).Select ' highlite entire row
Target.Activate ' select the cell

End Sub
You can simplify that first line of code like this...

Target.EntireRow.Select

[Transformer]

i found this code, it doesnt change the original highlighted color

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Rows(Target.Row).Select ' highlite entire row
Target.Activate ' select the cell

End Sub

And following can be used to highlight row and column both


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
Range(Target.EntireRow.Address & "," & Target.EntireColumn.Address).Select 'OR Union(Target.EntireRow, Target.EntireColumn).Select
Target.Activate
Application.EnableEvents = True

End Sub

[dypang87]

Although this is excellent, is there a code that will do the same, but, if selecting multiple cells (2 or more) with my mouse, the code becomes void?


i found this code, it doesnt change the original highlighted color

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Rows(Target.Row).Select ' highlite entire row
Target.Activate ' select the cell

End Sub

[Transformer]

You can check cells count.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count >= 2 Then Exit Sub
Application.EnableEvents = False
Range(Target.EntireRow.Address & "," & Target.EntireColumn.Address).Select 'OR Union(Target.EntireRow, Target.EntireColumn).Select
Target.Activate
Application.EnableEvents = True

End Sub

Although this is excellent, is there a code that will do the same, but, if selecting multiple cells (2 or more) with my mouse, the code becomes void?