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.

Highlighting the Rows of Selected Cells

by Allen Wyatt
(last updated December 29, 2014)

17

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Tools on Developer Tab are Unavailable

Want to add some macros to your workbook? What do you do if you try to add the macros but the program has disabled the tools?

Discover More

Automatically Opening a Document at a Specific Zoom Setting

Do you prefer to have your documents open at a specific zoom magnification? You can get whatever magnification you desire by ...

Discover More

Changing Pronoun Gender

Sometimes it is necessary to change the gender of various pronouns used in a document. If you have a stock document that ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

Easily Entering Dispersed Data

Need to enter information into a bunch of cells that aren't anywhere near each other in the worksheet? Here's a handy way to ...

Discover More

Ranges on Multiple Worksheets

Referring to a range of cells on the current worksheet is easy. But what if you want to refer to a range on a number of ...

Discover More

Using an Input Mask

When you are entering information in a worksheet, it sure would be handy to have a way to "mask" the information being ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 6?

2016-04-13 09:08:31

Frank

How can I put this into my 'Personal.xlsb' file so that I can run the macro from whatever spreadsheet I have open?

Thnaks


2016-01-07 08:23:27

Tushar

Awesome.

Thanks.



2015-01-19 08:46:05

dave

Curious.

SHIFT-SPACE highlights columns, not rows, for me. TAB & SHIFT-TAB move up & down the highlighted column.


2015-01-19 00:06:08

Randy

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?


2014-07-25 08:32:51

Chris

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


2014-07-23 23:15:36

pravin Chavan

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.


2014-01-05 19:28:16

Chris

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


2013-12-31 00:10:54

Derek Baldry

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


2013-12-31 00:02:30

Derek Baldry

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


2013-06-21 23:21:46

Chris

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


2013-06-21 22:44:56

Chris

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


2013-06-19 20:27:01

Chris

Hi Kate,

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

Chris


2013-06-18 09:17:26

Kate

Thanks Chris!

It worked really well for me and I was able to easily change my range of highlighted rows.


2013-06-17 20:45:56

Chris

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


2013-06-17 00:01:04

Chris

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


2013-06-14 09:29:20

Kate

Chris were you able to figure how to un- highlight the second row?


2013-06-07 23:51:21

Chris

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


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.