Highlight Current Row in Excel (VBA)

Printable View

10-16-2011, 03:28 AM
Admin

Highlight Current Row in Excel (VBA)

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.

Code:

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 !
10-18-2011, 09:04 PM
Nishant Choudhary

great :)
04-19-2012, 09:26 PM
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).

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = xlColorIndexNone Union(Target.EntireRow, Target.EntireColumn).Interior.ColorIndex = 6 End Sub
04-20-2012, 10:40 AM
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
04-20-2012, 11:28 AM
Rick Rothstein

Quote:

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.
04-20-2012, 01:48 PM
Rick Rothstein

Quote:

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
12-08-2012, 07:29 PM
salman8200

HI

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]
12-08-2012, 07:48 PM
Rick Rothstein

Quote:

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
12-09-2012, 11:10 AM
salman8200

Thanks
12-13-2012, 04:14 PM
hvg88

Quote:

Originally Posted by salman8200

Thanks

Thanks to all experts,
I want to know what for all worksheet to highlight current raw?
12-13-2012, 11:36 PM
Rick Rothstein

Quote:

Originally Posted by hvg88

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

Code:

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.
03-13-2013, 02:44 AM
luna

code w/o erasing original highlighted color

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

Quote:

Originally Posted by Rick Rothstein

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

Code:

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.
03-13-2013, 02:55 AM
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
03-19-2013, 08:48 AM
Rick Rothstein

Quote:

Originally Posted by 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

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

Target.EntireRow.Select
04-29-2013, 02:50 PM
Transformer

Quote:

Originally Posted by 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

And following can be used to highlight row and column both

Code:

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
07-31-2013, 04:08 AM
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?

Quote:

Originally Posted by 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
07-31-2013, 06:46 AM
Transformer

You can check cells count.

Code:

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

Quote:

Originally Posted by 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?