Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Highlighting rows using arrow keys

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a list of items in column A, with prices for those items in column B - Z. I want the viewer to easily see the the items and their corresponding prices, while using the arrow key to move up and down the item list. Is there a way to move up and down the item list with your arrow keys, while highlighting the whole row of data?

    Appreciate any help on this simple problem!

    Thanks

  2. #2
    New Member
    Join Date
    Mar 2002
    Location
    Brussels
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    quick and dirty: simply select tthe row by clicking on the leftmost column (before column A): the whole row will be highlighted

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was able to figure out this part, but I wanted to be able to scroll up and down on the item list only...., not clicking with my mouse on the row number.

    Any ideas??

    Sorry for not being too clear before!

    Thanks

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this...

    Go into the Visual basic editor and double click on the worksheet you want this to work for. Paste into it the following code...



    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = xlNone
    Target.EntireRow.Interior.ColorIndex = 36
    End Sub


    If you want this slightly altered, so that it does not remove heading formats, or so that it only works for certain rows or rows with certain values, reply again with details.

    Hope this works for you.

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    O.K... Sorry to have to say this... But (

    I have never done any work in VB. So I tried going into VB and double clicking on the sheet number and a window pop-up where I selected worksheet and section change. Pasted the code in and saved the update and went back into excel to see what damage I did. Nothing happened!! Got any ideas for this "bush-leaguer"??

    Thanks for the great help!

    [ This Message was edited by: christal on 2002-04-15 07:06 ]

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The code should work. Follow the below steps:

    1. In Excel, hit Alt+F11 to open VBA
    2. Look on the left of the VBA window for "VBAProject"
    3. Double click your sheet name that you want to hightlight rows.
    4. Look to the left for the big blank page and paste the following code:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    Cells.Interior.ColorIndex = xlNone 
    Target.EntireRow.Interior.ColorIndex = 3 
    End Sub
    5. Close the VBA window to return to excel. And click in a cell and watch the whole row be highlighted.
    6. Make sure that you have macros enabled (ie make sure you are not running on high level security).




    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That was a great help....BUT, I couldn't get it to work on the spreadsheet I needed it to work on. I got the function to work on a blank spreadsheet... so I know it wasn't something I did! I checked the security options, and those are O.K.!! Any further ideas??

    Thanks again!!

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    start again, then. go into the vb editor, double click on the sheet you need the code to work on, this should open the module.

    Delete everything in it, and paste the code.

    then save the workbook, close it, then open it again, sometimes this works!

    Good luck.

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Daleyman.....

    I was already ahead of you... I tried deleting the code and doing it over again, and it didn't work. But I did copy and paste the data to a new workbook, and cut and paste the VB code in again. And to my surprise it worked!! Yeppie!! But I will tell you one thing.. you cannot apply any patterns to cells after the VB code has been put in... so I had to delete the code, and then format the cells again. Thanks for all your help!!

    Christal

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was able to cut and paste the VB code into the editor, and YES, it finally worked! Problem now, is that the color that I had on my Excel spreadsheet is now gone! Does anyone have an idea, as to how I can keep the color format and still use the highlighted scrolling function?

    Much thanks!

    Christal

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •