Temporary Highlight Row- Worksheet Macro

MatthewJGale

New Member
Joined
Jun 30, 2008
Messages
6
Not sure the best way to go about this but I'm looking for a macro that I can add to the worksheet that will automatically highlight a row when I select any of the items in the row and un-highlights it when I click off of it.

1cf0k.gif


This is an example of what I am trying to accomplish. I'm simply trying to make things a little easier to read when I'm working with a call list. Often times I'll have other windows open that I'm tabbing between hence why I wanted it to highlight instead of just select the row.

Thanks!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
right click on the sheet tab (where the sheet name is) and select View Code from the list...

paste this code:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.EntireRow.Interior.Color = vbYellow Then
    Target.EntireRow.Interior.ColorIndex = xlNone
Else
    Target.EntireRow.Interior.Color = vbYellow
End If
End Sub
 
Upvote 0
Just as a note on iggydarsa's s code. This will "erase" any formatting that you have on the cells currently(Except Conditional formats).
 
Upvote 0
also keep in mind that it uses "Selection Change" event, it means the selection has to be changed, meaning clicking on A1 will highlight it but re-clicking on A1 won't do anything, you have to click on another cell on row #1 (ie: B1) in order to remove it
 
Upvote 0
right click on the sheet tab (where the sheet name is) and select View Code from the list...

paste this code:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.EntireRow.Interior.Color = vbYellow Then
    Target.EntireRow.Interior.ColorIndex = xlNone
Else
    Target.EntireRow.Interior.Color = vbYellow
End If
End Sub

That doesn't work for me. Try using the down arrow key to move down a few rows then the up arrow to move back up. The following works but it will clear all formatting

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.ClearFormats
Target.EntireRow.Interior.Color = vbYellow
End Sub
 
Upvote 0
Hmmm, this is almost what I'm looking for. The only problem is I need it to keep the formatting. The spreadsheet has a combination of phone numbers, zip codes, and several different date formats so I need to keep that intact. Any other suggestions? I've been playing around with the code given to see if I can come up with something but haven't had much luck so far.
 
Upvote 0
This quick and dirty solution will clear any previous interior colors, but preserves other formatting. It will highlight all selected rows, as wide as the usedrange.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
    Range(Cells(Target.Row, 1), Cells(Target.Row + Selection.Rows.Count - 1, UsedRange.Columns.Count)).Interior.Color = vbYellow
End Sub
 
Upvote 0
Hi, guys,

In the first place I would avoid this kind of code. Only use it when the other option is not available.

The main problem is that the UNDO will not work anymore. If you want to repair this, you can add a checkbox to the sheet and only when this is checked the code would run.

Now for the other option.
Do you use conditional format on your sheet? (and even if you would need it later, you could still use it) If not see
http://www.mrexcel.com/forum/showpost.php?p=1559873

you will NOT lose your undo

feel free to email me to get some more examples
subject: "color banding"
I will reply with a few excel files

kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top