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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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

R
  • Solved
VBA highlight specific Cells in row
Replies
2
Views
262
RedOctoberKnight
R
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
374
Eric W
Eric W
J
  • Question Question
Macro to pull Office 365 'Comments' onto new tab like I previously did with what's now 'Notes'
Replies
1
Views
119
jkouvel
J
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
B
  • Question Question
Macro to find and count between tabs based on highlighted color
Replies
5
Views
414
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,223,923
Messages
6,175,398
Members
452,640
Latest member
steveridge

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