+ Reply to Thread
Results 1 to 23 of 23

Ability to auto-highlight entire row and column of selected cell

  1. #1
    Registered User
    Join Date
    05-20-2010
    Location
    Shreveport, LA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Ability to auto-highlight entire row and column of selected cell

    Friends and Experts,

    My colleguaes and I work with a rather large data entry/archiving Excel 2007 spreadsheet on a daily basis. It contains several columns and rows that will not easily fit on one viewable screen. We have searched for the ability to highlight the entire data set across the row and down the column of the selected cell. This would allow us to navigate with the directional keys quickly and enter data assuring us we are on the correct row and column with a simple visual indication.

    I know that the number and letter of the row and column highlight on their own, but what we are needing is for the entire row and column of data to highlight as well.

    I have attached an example of what we would like to see.

    Does anyone know of a setting/function/method to accomplish this? I have searched around to no avail.
    Attached Images Attached Images
    Last edited by stephenminton; 05-20-2010 at 04:45 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Ability to auto-highlight entire row and column of selected cell

    There is no setting or function. You'll need to use VBA. I'm a novice but here's what I came up with. It highlights the entire row and column but otherwise does what you asked for. Right click on the sheet tab and "View Code" and paste this code in.
    Please Login or Register  to view this content.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-20-2010
    Location
    Shreveport, LA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Ability to auto-highlight entire row and column of selected cell

    Thanks! Works just like we needed.

  4. #4
    Registered User
    Join Date
    03-10-2012
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Ability to auto-highlight entire row and column of selected cell

    Thanks.
    It works nice.

    Can we xchange the color of highlighted row or column ?

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Ability to auto-highlight entire row and column of selected cell

    I would use .colorindex instead of .color and the values can be between 1 and 56 if you want to experiment or

    http://dmcritchie.mvps.org/excel/colors.htm

  6. #6
    Registered User
    Join Date
    09-06-2012
    Location
    VA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Ability to auto-highlight entire row and column of selected cell

    can we know if this highlights the cell that we are in currently? as we move the highlight shoud also move accordingly - say if we are in A1 then it should highlight A1 and if we move to B1 - it should unhighlight A1 and highlight B1

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Ability to auto-highlight entire row and column of selected cell

    In case it's unclear, this code highlights the entire row and column of the currently select cell. Not just the cell, the whole row and column.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  8. #8
    Registered User
    Join Date
    10-19-2012
    Location
    Deal, Kent
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Ability to auto-highlight entire row and column of selected cell

    This works wonderfully, my only problem is that copy and paste no longer work.

    Is there anyway around this?

    Many thanks

    Benjamin

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Ability to auto-highlight entire row and column of selected cell

    You would have to disable macros by activating the DESIGN MODE function while you were trying to cut/paste things on a sheet with that macro installed in it.

    In 2003, the DESIGN MODE icon is on the Control ToolBox menu.

  10. #10
    Registered User
    Join Date
    10-24-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Ability to auto-highlight entire row and column of selected cell

    Thanks,
    Works well

  11. #11
    Registered User
    Join Date
    06-18-2012
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Ability to auto-highlight entire row and column of selected cell

    Thanks ChemistB, the code works well in EXCEL 2010. However, it is removing all the prev. done color formatting. When I move the selected cell in a colored row/column, the input remains but color formatting's gone. After moving the selected cell away from colored row/column, everythings becomes plain white. Kindly see if you have any solution for this.

  12. #12
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Ability to auto-highlight entire row and column of selected cell

    @Fahads_neo,
    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    Ben Van Johnson

  13. #13
    Registered User
    Join Date
    05-10-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Ability to auto-highlight entire row and column of selected cell

    Thanks a lot

  14. #14
    Registered User
    Join Date
    06-08-2011
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Ability to auto-highlight entire row and column of selected cell

    thanks a lot

  15. #15
    Registered User
    Join Date
    04-01-2014
    Location
    Waycross, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Ability to auto-highlight entire row and column of selected cell

    VBA debug stops at ".Pattern = x1Solid"

    Any suggestions?

  16. #16
    Registered User
    Join Date
    04-01-2014
    Location
    Waycross, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Ability to auto-highlight entire row and column of selected cell

    VBA debug stops at ".Pattern = x1Solid"

    Any suggestions?

  17. #17
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Ability to auto-highlight entire row and column of selected cell

    It's xlSolid as in "Ex-cel", not "Ex-one"

  18. #18
    Registered User
    Join Date
    04-01-2014
    Location
    Waycross, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Ability to auto-highlight entire row and column of selected cell

    I copied his VBA from the post and tried to change per your instructions but it won't let me.

  19. #19
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Ability to auto-highlight entire row and column of selected cell

    ChemistB's original post has xlSolid. There's nothing in Excel that precludes you from editing text, or you can try copying it again, perhaps into Notepad, and checking it there before pasting it into Excel.

  20. #20
    Registered User
    Join Date
    04-01-2014
    Location
    Waycross, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Ability to auto-highlight entire row and column of selected cell

    I really appreciate you taking the time to help me with this!!

    Is the second character a capital "I" or a lowercase "L"?

    I haven't built macros in a while so I am somewhat rusty.

    In the posted macro, it looks like "1".

  21. #21
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Ability to auto-highlight entire row and column of selected cell

    Cutting and pasting eliminates the need to guess. It is a lowercase L, as in EXCEL (xl)

  22. #22
    Registered User
    Join Date
    04-01-2014
    Location
    Waycross, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Ability to auto-highlight entire row and column of selected cell

    That's what I did, copy & paste.

    This ia what it looks like in "debug" with "General" & "SelectionChange"

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 04-02-2014 at 05:47 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

  23. #23
    Registered User
    Join Date
    02-08-2013
    Location
    Louisiana, USA
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Ability to auto-highlight entire row and column of selected cell

    It should not be x1Solid it should be xlSolid.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1