Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Highlighting the Rows of Selected Cells

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Highlighting the Rows of Selected Cells.

Sometimes it is easy to lose track of where the selected cell is located in a worksheet. There are several ways you can locate the cell, but sometimes it would be handy to just have a way to highlight the whole row of the selected cell.

The easiest way to do this in Excel is to press Shift+Space Bar. The entire row is highlighted, and the selected cell remains the same. If you want to move to another cell in the same row (without changing the highlight), you can use Tab to move to the right and Shift+Tab to move to the left.

If you prefer to have Excel automatically highlight the row, you must rely upon a macro. The following one will do the trick:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static rr
    Static cc

    If cc <> "" Then
        With Columns(cc).Interior
            .ColorIndex = xlNone
        End With
        With Rows(rr).Interior
            .ColorIndex = xlNone
        End With
    End If

    r = Selection.Row
    c = Selection.Column
    rr = r
    cc = c

    With Columns(c).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
    With Rows(r).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
End Sub

Make sure you attach the macro to the worksheet you are using at the time. All the code does is highlight the row and column the active cell is at. When moving to another cell, the code remembers the previous cell (by using variables declared as Static) and removes the highlighting from the previous rows and columns. This code highlights both the current row and column. For just highlighting the row, remove the chunks of code with r and rr in them. The only real problem with this method is that if your sheet has any previous color-filled cells, these will be changed to NoFill, erasing any color that was there.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2457) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Highlighting the Rows of Selected Cells.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Frank    13 Apr 2016, 09:08
How can I put this into my 'Personal.xlsb' file so that I can run the macro from whatever spreadsheet I have open?

Thnaks
Tushar    07 Jan 2016, 08:23
Awesome.

Thanks.


dave    19 Jan 2015, 08:46
Curious.

SHIFT-SPACE highlights columns, not rows, for me. TAB & SHIFT-TAB move up & down the highlighted column.
Randy    19 Jan 2015, 00:06
I often highlight rows in tables on selection. While I might be over-simplifying things it seems so much easier then the code you have provided above.
Here is what I do;
I use just a tiny big of code in the Worksheet selection change for the range that should have the highlighting rows in effect, such as this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D8:R10000")) Is Nothing Then
Range("B1").Value = ActiveCell.Row
End If
End Sub

Then I use a Conditional formatting for B1 and the range, with any type of formatting you might like for the selected row. A Conditional formatting formula for the above would be:
=$B$1=ROW()
with an Applied To range of: =$D$8:$R$10000

That's it. No other coding is required and formats can be changed simply.
What are your thoughts on this?
Chris    25 Jul 2014, 08:32
Hi Pravin,

As I mentioned in my previous comment, for a full working code, please go to this link and view the one marked as the answer, it will have a green checkmark on it. If you would please vote the one with a green checkmark up. Voting the checkmark up will help to bump that answer to the top so people in the future will be able to locate it easier. Right now the one with the wrong answer is actually at the top because it has a vote up on it, while the correct answer remains at the bottom.

http://stackoverflow.com/questions/17005931/excel-2010-vb-script-highlight-row-issue
pravin Chavan    23 Jul 2014, 23:15
Hii I am pravin
I want to hightight active row in excel
But there is some error in below coding.


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

    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

I have this coding but when I go to next sheet and return to the coding sheet both row highlights previous ly selected row in sheet and active row
Please help me out for this.
Chris    05 Jan 2014, 19:28
Hi Guys,

Sorry that I didn't see your questions earlier. For some reason I never received an email notification saying there was an update question until now. If you are still looking for an answer to this, I posted the full solution here. I also condensed this code. It works the same, but less lines, which is better. It also corrects the issue of having 2 rows highlighted when you first open the spreadsheet. View the answer with the green checkmark next to it. I hope this helps. If you like it, please vote the answer up.

http://stackoverflow.com/questions/17005931/excel-2010-vb-script-highlight-row-issue
Derek Baldry    31 Dec 2013, 00:10
Most of my worksheets are complex and have colored cells. I use a shape (long red line) which I name "RowMarker" and the selection event macro to keep the red line under the active cell row. I also use a value in a cell (A1) to turn it on/off. Below is a simple example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'LINE MARKER
If [A1].value > 0 then
    ActiveSheet.Shapes("RowMarker").Top = ActiveCell.Offset(1, 0).Top
    ActiveSheet.Shapes("RowMarker").Left = Range("A:A").Left
End If
End Sub

There are many other variations of row or column highlighting that can be achieved with shapes providing your data array is not too big.

Hope this may be of interest to your readers.

Derek
 
Derek Baldry    31 Dec 2013, 00:02
Hello

If your worksheet has coloured cells you might try using a shape to indicate the activecell row. Insert a long red line (shape) and name it "RowMarker". Then use a selection change event macro, like the one below, to move the line below your activecell. You can add controls to your macro to restrict where it is used. Here I have used cell A1 to turn it on or off.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'LINE MARKER
If [A1].value > 0 then
    ActiveSheet.Unprotect
    ActiveSheet.Shapes("RowMarker").Top = ActiveCell.Offset(1, 0).Top
    ActiveSheet.Shapes("RowMarker").Left = Range("A:A").Left
    ActiveSheet.Protect
End If
End Sub

Perhaps this may be useful to your readers

Kind regards

Derek
Chris    21 Jun 2013, 23:21
OK, there was still a slight issue with the Microsoft part after looking at it, so this now is fully correct.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iFirstCol As Integer
Dim iLastCol As Integer
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim sColorRange As String
Dim iAlpha As Integer
Dim iRemainder As Integer
Dim iColor As Integer
Dim sFirstColumnConvert As String
Dim sLastColumnConvert As String

'''Only adjust the below numbers to fit your desired results.'''
iFirstCol = 1 'Change this number to the number of the first column that needs to be highlighted. Column A = 1.
iLastCol = 10 'Change this number to the number of the last column that needs to be highlighted. Column A = 1.
iFirstRow = 7 'Change this number to the number of the first row that needs to be highlighted.
iLastRow = 100 'Change this number to the number of the last row that needs to be highlighted.
iColor = 20 'Change this number to use a different highlight color.
'''End of changes, do not change anything else.'''


'Converts the numeric value of the first column to the equivalent letter.
iAlpha = Int(iFirstCol / 27)
iRemainder = iFirstCol - (iAlpha * 26)
If iAlpha > 0 Then
    sFirstColumnConvert = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    sFirstColumnConvert = sFirstColumnConvert & Chr(iRemainder + 64)
End If

'Converts the numeric value of the last column to the equivalent letter.
iAlpha = Int(iLastCol / 27)
iRemainder = iLastCol - (iAlpha * 26)
If iAlpha > 0 Then
    sLastColumnConvert = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    sLastColumnConvert = sLastColumnConvert & Chr(iRemainder + 64)
End If

'Sets the range of the entire area that is used to change the row color.
sColorRange = sFirstColumnConvert & iFirstRow & ":" & sLastColumnConvert & iLastRow

'The row highlight will only be applied if the selected range is within this if statement criteria.
If Target.Row > iFirstRow - 1 And Target.Row < iLastRow + 1 And Target.Column > iFirstCol - 1 And Target.Column < iLastCol + 1 Then
    ActiveSheet.Range(sColorRange).Interior.Color = xlNone 'Resets the color within the full range when cell selection changed.
    
    'Applies the colors to the row.
    For counter = iFirstCol To iLastCol
        With ActiveSheet.Cells(Target.Row, iFirstCol).Interior
            .ColorIndex = iColor
            .Pattern = xlSolid
        End With
        iFirstCol = iFirstCol + 1
    Next counter

End If
    

End Sub
Chris    21 Jun 2013, 22:44
Hi Kate,

Last update on this, I promise. I did write the entire code below without any help, with the exception for the small section on how to convert the column number into a letter. I couldn’t think of any way to do this myself, so I found the answer on Microsoft’s page that they supplied. I fully didn’t understand it at the time, but I wanted to, so I took a few minutes to figure out how it fully works. I do now fully understand it and made a small adjustment to their code. I honestly don’t see how the code that I posted before was properly converting it, using Microsoft’s part. I have made a couple of adjustments to Microsoft’s code to correct the issue that I noticed. This is now fully correct. I would suggest updating your code with this.

Chris

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iFirstCol As Integer
Dim iLastCol As Integer
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim sColorRange As String
Dim iAlpha As Integer
Dim iRemainder As Integer
Dim iColor As Integer
Dim sFirstColumnConvert As String
Dim sLastColumnConvert As String

'''Only adjust the below numbers to fit your desired results.'''
iFirstCol = 1 'Change this number to the number of the first column that needs to be highlighted. Column A = 1.
iLastCol = 10 'Change this number to the number of the last column that needs to be highlighted. Column A = 1.
iFirstRow = 7 'Change this number to the number of the first row that needs to be highlighted.
iLastRow = 100 'Change this number to the number of the last row that needs to be highlighted.
iColor = 20 'Change this number to use a different highlight color.
'''End of changes, do not change anything else.'''


'Converts the numeric value of the first column to the equivalent letter.
iAlpha = Int(iFirstCol / 27)
iRemainder = iFirstCol - (iAlpha * 26)
If iAlpha > 0 Then
    sFirstColumnConvert = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    sFirstColumnConvert = Chr(iRemainder + 64)
End If

'Converts the numeric value of the last column to the equivalent letter.
iAlpha = Int(iLastCol / 27)
iRemainder = iLastCol - (iAlpha * 26)
If iAlpha > 0 Then
    sLastColumnConvert = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    sLastColumnConvert = Chr(iRemainder + 64)
End If

'Sets the range of the entire area that is used to change the row color.
sColorRange = sFirstColumnConvert & iFirstRow & ":" & sLastColumnConvert & iLastRow

'The row highlight will only be applied if the selected range is within this if statement criteria.
If Target.Row > iFirstRow - 1 And Target.Row < iLastRow + 1 And Target.Column > iFirstCol - 1 And Target.Column < iLastCol + 1 Then
    ActiveSheet.Range(sColorRange).Interior.Color = xlNone 'Resets the color within the full range when cell selection changed.
    
    'Applies the colors to the row.
    For counter = iFirstCol To iLastCol
        With ActiveSheet.Cells(Target.Row, iFirstCol).Interior
            .ColorIndex = iColor
            .Pattern = xlSolid
        End With
        iFirstCol = iFirstCol + 1
    Next counter

End If
    

End Sub
Chris    19 Jun 2013, 20:27
Hi Kate,

You’re welcome. I am glad this worked for your needs and that it was simple to use.

Chris
Kate    18 Jun 2013, 09:17
Thanks Chris!

It worked really well for me and I was able to easily change my range of highlighted rows.
Chris    17 Jun 2013, 20:45
Hi Kate,

I realized I forgot to add one “and” statement to the “if” statement. Please use this instead.

Chris


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iFirstCol As Integer
Dim iLastCol As Integer
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim sColorRange As String
Dim iAlpha As Integer
Dim iRemainder As Integer
Dim iColor As Integer
Dim sFirstColumnConvert As String
Dim sLastColumnConvert As String

'''Only adjust the below numbers to fit your desired results.'''
iFirstCol = 1 'Change this number to the number of the first column that needs to be highlighted. Column A = 1.
iLastCol = 10 'Change this number to the number of the last column that needs to be highlighted. Column A = 1.
iFirstRow = 7 'Change this number to the number of the first row that needs to be highlighted.
iLastRow = 100 'Change this number to the number of the last row that needs to be highlighted.
iColor = 20 'Change this number to use a different highlight color.
'''End of changes, do not change anything else.'''


'Converts the numeric value of the first column to the equivalent letter.
iAlpha = Int(iFirstCol / 27)
iRemainder = iFirstCol - (iAlpha * 26)
If iAlpha > 0 Then
    sLastColumnConvert = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    sFirstColumnConvert = ConvertToLetter & Chr(iRemainder + 64)
End If

'Converts the numeric value of the last column to the equivalent letter.
iAlpha = Int(iLastCol / 27)
iRemainder = iLastCol - (iAlpha * 26)
If iAlpha > 0 Then
    sLastColumnConvert = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    sLastColumnConvert = ConvertToLetter & Chr(iRemainder + 64)
End If

'Sets the range of the entire area that is used to change the row color.
sColorRange = sFirstColumnConvert & iFirstRow & ":" & sLastColumnConvert & iLastRow

'The row highlight will only be applied if the selected range is within this if statement criteria.
If Target.Row > iFirstRow - 1 And Target.Row < iLastRow + 1 And Target.Column > iFirstCol - 1 And Target.Column < iLastCol + 1 Then
    ActiveSheet.Range(sColorRange).Interior.Color = xlNone 'Resets the color within the full range when cell selection changed.
    
    'Applies the colors to the row.
    For counter = iFirstCol To iLastCol
        With ActiveSheet.Cells(Target.Row, iFirstCol).Interior
            .ColorIndex = iColor
            .Pattern = xlSolid
        End With
        iFirstCol = iFirstCol + 1
    Next counter

End If
    

End Sub
Chris    17 Jun 2013, 00:01
Hi Kate,

I came up with my own code. I don’t see a solution to the code listed here. My code is a lot longer than what it really needs to be, about 4 times as long. The reason for this is because it is extremely easy to update. If I posted my original short version, there might have been a fair amount to update by anyone who wanted to use it. In this version the only thing that you will need to edit are the numeric values for the 5 listed integers. After you copy my code into excel, you should easily be able to read it and figure out what needs to be updated. I placed good notes within the code and the integers that need to be edited are grouped together.

How this works is that it only allows the rows to be highlighted that you specify, along with what columns should be highlighted within the row (the entire column will not be highlighted just the row). Like if you only want rows 7 – 100 highlighted then you would defines this by changing the integer value numbers. Then if you only want columns A – F highlighted of that row, you define the numeric value of that column by the integer. The reason why I had to do it this way is because as we know, the code that was supplied to us here will not remove the highlight from the row when the spreadsheet is saved, closed, and reopened. With my code, the highlight will be removed each time a new cell is clicked on, within your specified range defined in the integers.

I hope this makes sense. I think it will be extremely clear once you copy the code into excel and look at it reading my comments within the code of what to edit. Copy everything below my name for the code. If you are still confused, let me know and I will try to help you. I hope it works for your needs.

Chris


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iFirstCol As Integer
Dim iLastCol As Integer
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim sColorRange As String
Dim iAlpha As Integer
Dim iRemainder As Integer
Dim iColor As Integer
Dim sFirstColumnConvert As String
Dim sLastColumnConvert As String

'''Only adjust the below numbers to fit your desired results.'''
iFirstCol = 1 'Change this number to the number of the first column that needs to be highlighted. Column A = 1.
iLastCol = 10 'Change this number to the number of the last column that needs to be highlighted. Column A = 1.
iFirstRow = 7 'Change this number to the number of the first row that needs to be highlighted.
iLastRow = 100 'Change this number to the number of the last row that needs to be highlighted.
iColor = 20 'Change this number to use a different highlight color.
'''End of changes, do not change anything else.'''


'Converts the numeric value of the first column to the equivalent letter.
iAlpha = Int(iFirstCol / 27)
iRemainder = iFirstCol - (iAlpha * 26)
If iAlpha > 0 Then
    sLastColumnConvert = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    sFirstColumnConvert = ConvertToLetter & Chr(iRemainder + 64)
End If

'Converts the numeric value of the last column to the equivalent letter.
iAlpha = Int(iLastCol / 27)
iRemainder = iLastCol - (iAlpha * 26)
If iAlpha > 0 Then
    sLastColumnConvert = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    sLastColumnConvert = ConvertToLetter & Chr(iRemainder + 64)
End If

'Sets the range of the entire area that is used to change the row color.
sColorRange = sFirstColumnConvert & iFirstRow & ":" & sLastColumnConvert & iLastRow

'The row highlight will only be applied if the selected range is within this if statement criteria.
If Target.Row > iFirstRow - 1 And Target.Row < iLastRow + 1 And Target.Column < iLastCol + 1 Then
    ActiveSheet.Range(sColorRange).Interior.Color = xlNone 'Resets the color within the full range when cell selection changed.
    
    'Applies the colors to the row.
    For counter = iFirstCol To iLastCol
        With ActiveSheet.Cells(Target.Row, iFirstCol).Interior
            .ColorIndex = iColor
            .Pattern = xlSolid
        End With
        iFirstCol = iFirstCol + 1
    Next counter

End If
    

End Sub
Kate    14 Jun 2013, 09:29
Chris were you able to figure how to un- highlight the second row?
Chris    07 Jun 2013, 23:51
This is awesome, but I did notice a slight problem that I am trying to figure out. When you save the spreadsheet with a row highlighted, close out of the spreadsheet and reopen it, then select another row, the old row is still highlighted. Now you have two rows highlighted. To resolve this you need to click on the row again. Is there a way to automatically remove the highlighted row when the spreadsheet is opened? It does already remove the highlight from the row when you select another tab in the spreadsheet and go back to the current one. On a side note, I have it so only the row is selected and not the column. Thanks
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.