Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Highlight Current Row in Excel (VBA)

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

    Lightbulb 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 !
    Last edited by Admin; 04-20-2012 at 12:06 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Junior Member
    Join Date
    Sep 2011
    Posts
    8
    Rep Power
    0
    great

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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

  4. #4
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    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
    Last edited by Rajan_Verma; 04-20-2012 at 10:44 AM.

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rajan_Verma View Post
    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.
    Last edited by Rick Rothstein; 04-20-2012 at 01:45 PM.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rajan_Verma View Post
    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
    Last edited by Rick Rothstein; 04-20-2012 at 01:55 PM.

  7. #7
    Junior Member
    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0

    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]

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by salman8200 View Post
    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

  9. #9
    Junior Member
    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Thanks

  10. #10
    Junior Member
    Join Date
    Dec 2012
    Posts
    1
    Rep Power
    0
    Quote Originally Posted by salman8200 View Post
    Thanks
    Thanks to all experts,
    I want to know what for all worksheet to highlight current raw?

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2013, 01:08 PM
  2. Highlight Active Cell’s Row and Column
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  3. Replies: 6
    Last Post: 05-16-2013, 09:56 AM
  4. Help- Locking column basis current date.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 1
    Last Post: 03-25-2013, 04:44 PM
  5. Moving Current Latest Data To New Workbook
    By Terry in forum Excel Help
    Replies: 1
    Last Post: 01-19-2013, 12:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •