Temporary Highlight Row- Worksheet Macro

M

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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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
iggydarsa said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

O
  • Question Question
Remove Queries from a temporary file
Replies
2
Views
118
John_w
J
R
  • Solved
VBA highlight specific Cells in row
Replies
2
Views
331
RedOctoberKnight
R
K
  • Solved
Help with Hightlighting the Active Row - How to apply code to all new sheets created
Replies
5
Views
202
kellman
K
D
  • Question Question
Running 2 Macros sequentially when opening a sheet
Replies
5
Views
233
Alex Blakenburg
A
M
  • Question Question
Index Match formula pulling from a longer list with blank row spaces or unwanted data into a shorter list without row spaces
Replies
5
Views
446
Eric W
Eric W

Forum statistics

Threads
1,226,740
Messages
6,192,742
Members
453,755
Latest member
IQBS

Share this page

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