Highlight active row in Excel

tg93135 · 01-11-2006, 07:23 AM

I would like an easy way to highlight an active row in Excel. I'm not really proficient in Visual Basic, so if that's the only way, your specific direction would be greatly appreciate. Thanks in advace.

**Glaswegian** · 01-12-2006, 01:30 PM

Hi

Other than manually highlighting the row, you will have to use VBA. There are several very involved methods, but one easy one is to pop up a message box asking you to confirm that you want the row highlighted. Otherwise, every time you click on any row it will be highlighted. Will you want to remove the highlight at any time? Will you be highlighting more than one row?

tg93135 · 01-17-2006, 12:14 PM

thanks for responding. i would not want to highlight more than 1 row at a time and a pop up message would be okay. basically, whatever the easiest way would be, would work for me. But like I said, i've only played around with VBasic a little. Thanks for your continued help.

Todd

**Glaswegian** · 01-17-2006, 01:00 PM

Hi Todd

See if this works for you. This code uses the Worksheet Selection Change Event - when you click on a cell in rows 1 to 20 a pop up will appear asking if you want to highlight the entire row. If you say YES the entire row is coloured red - if you say NO nothing happens. You can change the colour to suit. I've used a cut off of the first 20 rows but you can change that as well. Copy the code below, then right click the relevant sheet tab, select 'View Code' and paste the code to the right pane. Press Alt+F11 to return to Excel.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myReply
Dim myRow As Integer

myRow = Target.Row
If myRow > 20 Then Exit Sub

myReply = MsgBox("Do you want to highlight this row", vbYesNo)
    If myReply = vbNo Then Exit Sub
    ActiveCell.EntireRow.Interior.ColorIndex = 3
End Sub

Let me know if you have any problems with this or need any elements changed.

HTH

Regards

tg93135 · 01-17-2006, 02:24 PM

howdy. Thanks so much for your help on this. Can a couple of things be changed? Firstly, can you write it so the active row is highlighted automatically without a prompt? Maybe something like "do you want the active row highlighted on this sheet at all times" but i'll leave that to you. Basically, if i'm on row 1, only row 1 is highlighted, on row 5, only row 5 is highlighted, etc. Also, can we change the color to say, yellow [or tell me the value # for the colors]

Thanks a ton for your help.

Todd

tg93135 · 01-17-2006, 02:26 PM

howdy. Thanks so much for your help on this. Can a couple of things be changed? Firstly, can you write it so the active row is highlighted automatically without a prompt? Maybe something like "do you want the active row highlighted on this sheet at all times" but i'll leave that to you. Basically, if i'm on row 1, only row 1 is highlighted, on row 5, only row 5 is highlighted, etc. Also, can we change the color to say, yellow [or tell me the value # for the colors]

Also, will there be any easy way to do this each time? Macro? or do i need to copy and paste the VB language each time I open a spreadsheet where I desire the highlighting?

Thanks a ton for your help.

Todd

**Glaswegian** · 01-17-2006, 02:42 PM

Hi Todd

No problem on the changes, but I'd like to clarify some things first.

If I take out the pop up, as things are, it will highlight every row when you click on a cell in that row. So you will need to "unhighlight" (if there is such a word!) a row already highlighted before the next one is highlighted? An Event procedure such as this relates specifically to the sheet in question - you can't 'call' this procedure from another file.

Regards

tg93135 · 01-17-2006, 03:12 PM

Well, unfortunately, either way would be kind of unpractical. I often use sheets with a couple hundred rows with data [hence looking for a way to make numbers more easily read on the active row]. Having to either click okay each time in addition to having to unhighlite the previous row would become somewhat time consuming. C'mon, you're working your magic wonderfully! Isn't there another way?? You're so close....Thanks....

**Glaswegian** · 01-17-2006, 03:21 PM

Todd

Try this - it goes in the ThisWorkbook Module and will therefore work for any sheet in the workbook.

Add Code to ThisWorkbook Module

1. Right click on the Excel icon beside ‘File’ on the Menu Bar.
2. Choose 'View Code' from the menu
3. Paste the code to the right pane.

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range

On Error Resume Next
Target.EntireRow.Interior.ColorIndex = 6
OldRange.EntireRow.Interior.ColorIndex = xlColorIndexNone
Set OldRng = Target

End Sub

When you click on a cell, the row will turn yellow. Click on another cell and the new row turns yellow and the old one returns to normal. As I said, works for any sheet in the workbook.

Regards

tg93135 · 01-17-2006, 03:34 PM

soooo close, however the "old" range isn't going back to "non-highlited"

and just to clarify, I would need to paste the VB language into the code window each time I open a spreadsheet i want to use this on, right? or is there a shortcut?

THANKS THANKS

**Glaswegian** · 01-17-2006, 03:39 PM

Apologies - code blooper. Use this

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range

On Error Resume Next
Target.EntireRow.Interior.ColorIndex = 6
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
Set OldRng = Target

End Sub

Also make sure you deleted the previous code that showed the pop up - otherwise there will be all sorts of conflicts.

Other than that it works fine.

tg93135 · 01-18-2006, 07:27 AM

You're the bomb!!!!! One more question, since i will use this sporadically on spreadsheet, is there a shortcut to "turn it on" other than pasting the code each time? Thanks tons for your help on this.

Todd

**Glaswegian** · 01-18-2006, 12:42 PM

No shortcut I'm afraid Todd.

You'll need to copy and paste it for each workbook. But once in the workbook it will work for each sheet in that workbook without any further copy/paste.

Regards

tg93135 · 01-19-2006, 07:01 AM

Well, i guess this may be my last transmission, thanks for you help on this. A macro cannot be used as a shortcut?

Thanks.

**Glaswegian** · 01-19-2006, 08:28 AM

Not really. The code is an Event code - it relies on a change on the worksheet to fire. Macros generally require some kind of action from the user - clicking a command button, for example - perhaps possible but unlikely. Certainly way beyond my knowledge. It won't be much effort to do a simple copy and paste - perhaps copy the code to a Word for suture reference?

Regards

tg93135 · 01-19-2006, 08:35 AM

Thanks For Everything!

sidjaya86 · 07-22-2009, 07:09 AM

the problem i faced while using the code was although the cells are highlighted but when you copy a cell and try to drag down copy in the column you are in, the copied cell is deselected and you can't paste. Glaswegian, can you provide an alternate code which takes care of this issue?

Domking · 08-03-2009, 08:00 AM

I have been using the below code and I have not had problems using the copy command or drag down copy. The code does highlight the active row and column, not just the row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RngRow As Range
Dim RngCol As Range
Dim RngFinal As Range
Dim Row As Long
Dim Col As Long

Cells.Interior.ColorIndex = xlNone

Row = Target.Row
Col = Target.Column

Set RngRow = Range("A" & Row, Target)
Set RngCol = Range(Cells(1, Col), Target)
Set RngFinal = Union(RngRow, RngCol)

RngFinal.Interior.ColorIndex = 6

End Sub

kpo3040 · 10-19-2009, 12:49 PM

i tried using these codes and i can't excel to recognize the modules. it almost seems like it has something to do with the 'byvaltarget as range'. i even tried creating my own macro and then just pasting this code in, and the macro disappears and just won't run. any ideas?

**Glaswegian** · 10-19-2009, 02:02 PM

Hi and welcome.

the code must be pasted into the Sheet module. One way to do this is to right click the sheet tab and select 'View Code' - when the VBE opens, that's where you post the code.

01-11-2006, 07:23 AM	#1 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	Highlight active row in Excel I would like an easy way to highlight an active row in Excel. I'm not really proficient in Visual Basic, so if that's the only way, your specific direction would be greatly appreciate. Thanks in advace.

01-12-2006, 01:30 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Other than manually highlighting the row, you will have to use VBA. There are several very involved methods, but one easy one is to pop up a message box asking you to confirm that you want the row highlighted. Otherwise, every time you click on any row it will be highlighted. Will you want to remove the highlight at any time? Will you be highlighting more than one row? __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-17-2006, 12:14 PM	#3 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	highlight row in excel thanks for responding. i would not want to highlight more than 1 row at a time and a pop up message would be okay. basically, whatever the easiest way would be, would work for me. But like I said, i've only played around with VBasic a little. Thanks for your continued help. Todd

01-17-2006, 01:00 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Todd See if this works for you. This code uses the Worksheet Selection Change Event - when you click on a cell in rows 1 to 20 a pop up will appear asking if you want to highlight the entire row. If you say YES the entire row is coloured red - if you say NO nothing happens. You can change the colour to suit. I've used a cut off of the first 20 rows but you can change that as well. Copy the code below, then right click the relevant sheet tab, select 'View Code' and paste the code to the right pane. Press Alt+F11 to return to Excel. Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myReply Dim myRow As Integer myRow = Target.Row If myRow > 20 Then Exit Sub myReply = MsgBox("Do you want to highlight this row", vbYesNo) If myReply = vbNo Then Exit Sub ActiveCell.EntireRow.Interior.ColorIndex = 3 End Sub Let me know if you have any problems with this or need any elements changed. HTH Regards __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-17-2006, 02:24 PM	#5 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	Active Row Highlight howdy. Thanks so much for your help on this. Can a couple of things be changed? Firstly, can you write it so the active row is highlighted automatically without a prompt? Maybe something like "do you want the active row highlighted on this sheet at all times" but i'll leave that to you. Basically, if i'm on row 1, only row 1 is highlighted, on row 5, only row 5 is highlighted, etc. Also, can we change the color to say, yellow [or tell me the value # for the colors] Thanks a ton for your help. Todd

01-17-2006, 02:26 PM	#6 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	Active Row Highlight howdy. Thanks so much for your help on this. Can a couple of things be changed? Firstly, can you write it so the active row is highlighted automatically without a prompt? Maybe something like "do you want the active row highlighted on this sheet at all times" but i'll leave that to you. Basically, if i'm on row 1, only row 1 is highlighted, on row 5, only row 5 is highlighted, etc. Also, can we change the color to say, yellow [or tell me the value # for the colors] Also, will there be any easy way to do this each time? Macro? or do i need to copy and paste the VB language each time I open a spreadsheet where I desire the highlighting? Thanks a ton for your help. Todd

01-17-2006, 02:42 PM	#7 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Todd No problem on the changes, but I'd like to clarify some things first. If I take out the pop up, as things are, it will highlight every row when you click on a cell in that row. So you will need to "unhighlight" (if there is such a word!) a row already highlighted before the next one is highlighted? An Event procedure such as this relates specifically to the sheet in question - you can't 'call' this procedure from another file. Regards __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-17-2006, 03:12 PM	#8 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	highlite row Well, unfortunately, either way would be kind of unpractical. I often use sheets with a couple hundred rows with data [hence looking for a way to make numbers more easily read on the active row]. Having to either click okay each time in addition to having to unhighlite the previous row would become somewhat time consuming. C'mon, you're working your magic wonderfully! Isn't there another way?? You're so close....Thanks.... Last edited by tg93135; 01-17-2006 at 03:14 PM.

01-17-2006, 03:21 PM	#9 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Todd Try this - it goes in the ThisWorkbook Module and will therefore work for any sheet in the workbook. Add Code to ThisWorkbook Module 1. Right click on the Excel icon beside ‘File’ on the Menu Bar. 2. Choose 'View Code' from the menu 3. Paste the code to the right pane. Code: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Static OldRng As Range On Error Resume Next Target.EntireRow.Interior.ColorIndex = 6 OldRange.EntireRow.Interior.ColorIndex = xlColorIndexNone Set OldRng = Target End Sub When you click on a cell, the row will turn yellow. Click on another cell and the new row turns yellow and the old one returns to normal. As I said, works for any sheet in the workbook. Regards __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-17-2006, 03:34 PM	#10 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	highlite row soooo close, however the "old" range isn't going back to "non-highlited" and just to clarify, I would need to paste the VB language into the code window each time I open a spreadsheet i want to use this on, right? or is there a shortcut? THANKS THANKS

01-17-2006, 03:39 PM	#11 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Apologies - code blooper. Use this Code: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Static OldRng As Range On Error Resume Next Target.EntireRow.Interior.ColorIndex = 6 OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone Set OldRng = Target End Sub Also make sure you deleted the previous code that showed the pop up - otherwise there will be all sorts of conflicts. Other than that it works fine. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-18-2006, 07:27 AM	#12 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	You're the bomb!!!!! One more question, since i will use this sporadically on spreadsheet, is there a shortcut to "turn it on" other than pasting the code each time? Thanks tons for your help on this. Todd

01-18-2006, 12:42 PM	#13 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	No shortcut I'm afraid Todd. You'll need to copy and paste it for each workbook. But once in the workbook it will work for each sheet in that workbook without any further copy/paste. Regards __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-19-2006, 07:01 AM	#14 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	excel highlite row Well, i guess this may be my last transmission, thanks for you help on this. A macro cannot be used as a shortcut? Thanks.

01-19-2006, 08:28 AM	#15 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Not really. The code is an Event code - it relies on a change on the worksheet to fire. Macros generally require some kind of action from the user - clicking a command button, for example - perhaps possible but unlikely. Certainly way beyond my knowledge. It won't be much effort to do a simple copy and paste - perhaps copy the code to a Word for suture reference? Regards __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-19-2006, 08:35 AM	#16 (permalink)
tg93135 Registered User Join Date: Aug 2005 Posts: 11 OS: xp	highlite Thanks For Everything!

07-22-2009, 07:09 AM	#17 (permalink)
sidjaya86 Registered User Join Date: Jul 2009 Posts: 1 OS: WinXP	Re: Highlight active row in Excel the problem i faced while using the code was although the cells are highlighted but when you copy a cell and try to drag down copy in the column you are in, the copied cell is deselected and you can't paste. Glaswegian, can you provide an alternate code which takes care of this issue?

08-03-2009, 08:00 AM	#18 (permalink)
Domking Registered User Join Date: Aug 2009 Posts: 1 OS: xp	Re: Highlight active row in Excel I have been using the below code and I have not had problems using the copy command or drag down copy. The code does highlight the active row and column, not just the row. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim RngRow As Range Dim RngCol As Range Dim RngFinal As Range Dim Row As Long Dim Col As Long Cells.Interior.ColorIndex = xlNone Row = Target.Row Col = Target.Column Set RngRow = Range("A" & Row, Target) Set RngCol = Range(Cells(1, Col), Target) Set RngFinal = Union(RngRow, RngCol) RngFinal.Interior.ColorIndex = 6 End Sub

10-19-2009, 12:49 PM	#19 (permalink)
kpo3040 Registered User Join Date: Oct 2009 Posts: 2 OS: vista	Re: Highlight active row in Excel i tried using these codes and i can't excel to recognize the modules. it almost seems like it has something to do with the 'byvaltarget as range'. i even tried creating my own macro and then just pasting this code in, and the macro disappears and just won't run. any ideas?

10-19-2009, 02:02 PM	#20 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Highlight active row in Excel Hi and welcome. the code must be pasted into the Sheet module. One way to do this is to right click the sheet tab and select 'View Code' - when the VBE opens, that's where you post the code. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner