# Highlight active row in Excel



## tg93135

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

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

*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


----------



## Glaswegian

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

*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


----------



## tg93135

*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


----------



## Glaswegian

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

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


----------



## Glaswegian

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

*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


----------



## Glaswegian

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.:wink: 

Other than that it works fine. :grin:


----------



## tg93135

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

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

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


----------



## Glaswegian

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

*highlite*

Thanks For Everything!


----------



## sidjaya86

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

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

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

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.


----------



## kpo3040

thanks so much!!!


----------



## joelicaral

Domking....thank you so much! This worked perfectly and was exactly what I was looking for.


----------



## Just Jules

Hi Can you help pls. Have used the visual basic text quoted above and it is working great. Just one question; when I find the cell I was searching for, I normally use a fill colour to acknowledge to myself that I am happy with the data in that specific cell. However, the fill colour doesnt stay when i move off again because of the highlighted column/row, the cell fill colour disappears. 

Other than using italics or bold to overcome this problem, is there a quick fix?


----------



## RSpecianjr

Hey Just Jules,

If just the active cell was being highlighted, it might be easy. The problem is that there is an entire row and/or column being highlighted. Which could mean lots of different formats. :sigh: So the code that is posted in this thread may not be what you want. 

*However, this is probably what your looking for:*

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

On Error Resume Next
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
'Conditional Formatting for Column
Target.EntireColumn.FormatConditions.Add Type:=xlTextString, String:= _
"A Very Improbable String Of Text To Be Found In Your Workbook.", TextOperator _
:=xlDoesNotContain
Target.EntireColumn.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Target.EntireColumn.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Target.EntireColumn.FormatConditions(1).StopIfTrue = False

'Conditional Formatting for Row
Target.EntireRow.FormatConditions.Add Type:=xlTextString, String:= _
"A Very Improbable String Of Text To Be Found In Your Workbook.", TextOperator _
:=xlDoesNotContain
Target.EntireRow.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Target.EntireRow.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Target.EntireRow.FormatConditions(1).StopIfTrue = False

Set OldRng = Target

End Sub


It uses conditional formatting instead of changing the cells interior colors. When a cell in the selected Row/Column doesn't have "A Very Improbable String Of Text To Be Found In Your Workbook.", then it gets formatted With the highlighter yellow. When you select a different cell, it removes the previous conditional formatting and adds the new one. The cells will retain their original formatting and all is well and good. :wink:

If you have any questions or problems with it let me know.

Hope this helps,

Robert Specian Jr.


----------



## Just Jules

Hi RSpecianJr

thanks for your prompt reply. I now quite like the fact that the whole row is highlighted, so I have started to change my cells to bold and italics to denote an accepted cell value. 

the only problem I now have is if I need to copy the data within the row that is highlighted, as soon a you press copy and move off the row, the copy requests becomes invalid. Is there a quick way to fix this??


----------



## RSpecianjr

Hey Just Jules,

Sorry, I didn't think about that when I first wrote this. I've got an adapted version for you:

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

On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
Exit Sub
End If

OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
'Conditional Formatting for Column
Target.EntireColumn.FormatConditions.Add Type:=xlTextString, String:= _
"A Very Improbable String Of Text To Be Found In Your Workbook.", TextOperator _
:=xlDoesNotContain
Target.EntireColumn.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Target.EntireColumn.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Target.EntireColumn.FormatConditions(1).StopIfTrue = False

'Conditional Formatting for Row
Target.EntireRow.FormatConditions.Add Type:=xlTextString, String:= _
"A Very Improbable String Of Text To Be Found In Your Workbook.", TextOperator _
:=xlDoesNotContain
Target.EntireRow.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Target.EntireRow.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Target.EntireRow.FormatConditions(1).StopIfTrue = False

Set OldRng = Target
Application.CutCopyMode = False
End Sub

This version will work with copy/cut/paste. When you are done copying/pasting hit esc to clear the clipboard and start the highlighter again. Cut/Paste will automatically clear the clipboard, so the highlighter will come back after you paste.

This is just a quick fix, if you really need it to work without pressing esc. I'll try and get a better version when I have some more time on my hands. Very useful macro = ).

Hope this helps,

Robert Specian Jr.


----------



## Just Jules

Hey Robert. Keeps coming back with a compile error: Named argument could not be found.

There is text highlighted within the first column format formula

String: -

Your help is very much appreciated.


----------



## RSpecianjr

Hey Just Jules,

The code above should work. Especially if the first code I posted did. If not there were only two pieces I added to the my original post.

Put this right after the "On error resume next" line:

If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
Exit Sub
End If

and 

Application.CutCopyMode = False

will be last thing before "end sub"

If its still giving you and error, I can take a look at it.

Hope this helps,

Robert Specian Jr.


----------



## kenatu

guys thank you for this thread... i got both to work...however there is a little bug in the code....do you realize that the undo function does work as it should anymore when the code is in the sheet?..... it only stores the last change and nothing more....does anyone know how to fix this?

thanks again


----------



## RSpecianjr

Hey Kenatu,

Is it possible to do? Yes, just about anything is possible with code. However when a macro is run, the undo function will have been 'cleared'. So without writing a lot of code to capture the cell changes you make as you go, then referencing them to undo. I cannot think of any quick, or even easy, fix this.

Perhaps making it an add-in would allow you to retain previous actions? I've never worked with add-in's before so someone else would have to answer that one.

Regards,

Robert Specian Jr.


----------



## Just Jules

I tried it again. I didnt enter the first codes you gave me which is why I didnt realise the compile error the first time round. 

It is still coming back with the same compile error: Named argument could not be found.

There is text highlighted within the first column format formula

String: -

Sorry, I know we are so close and this would help me so much. Im very grateful for your help on this.


----------



## Just Jules

RSpecianJr. 

I did carry out the changes noted above as requested.


----------



## RSpecianjr

Hey Just Jules,

Can you post, or send me, a copy of the workbook?

Thanks,

Robert Specian Jr.


----------



## Just Jules

Hi There., how would I email a copy to you please?


----------

