+ Reply to Thread
Results 1 to 23 of 23

Highlighting the row of the current active cell [How To]

  1. #1
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Question Highlighting the row of the current active cell [How To]

    Using Excel 2007

    Is there a way to make it highlight the (entire) row of the current active cell—the one you have selected at that moment?

    Just temporarily, as a guide. Darken the background/fill relative to the rest so you can find where you are when you are working with sets of data stretched across many columns per row and have a full and busy sheet. It already does something similar to the row and column headers around the sheet, I would like it to go a step further.
    Last edited by fluentox; 01-22-2016 at 09:21 AM. Reason: Previous title was "Simple obvious function I can't seem to find"

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Simple obvious function I can't seem to find

    Please take a few minutes to re-read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Simple obvious function I can't seem to find

    Quote Originally Posted by shg View Post
    Please take a few minutes to re-read the forum rules, and then amend your thread title accordingly.

    Thanks.
    I did not see a message asking me to read the rules when I signed up, and I looked on the forum for a stickied post containing the rules before I posted this, but did not see any.

    If I may kindly ask: Could you direct me to these rules or tell me which one I've broken?

  4. #4
    Valued Forum Contributor
    Join Date
    10-06-2008
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    365

    Re: Simple obvious function I can't seem to find

    You have to change the title (rule nr. 1). The Forum Rules can be found at the top of each forum (the blue-grey banner at the top of the page) or in the Forums View when you first log on.

  5. #5
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Simple obvious function I can't seem to find

    Thanks. Done.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Highlighting the row of the current active cell [How To]

    Thanks .

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Highlighting the row of the current active cell [How To]

    I found this code at https://msdn.microsoft.com/en-us/lib.../ff820716.aspx that highlights both the column and the row of the active cell. This allows you to follow either the row or the column in the manner that you previously described.

    Select and copy the code then right click on the worksheet tab, View Code and Paste the code into the window that is presented.

    Please Login or Register  to view this content.
    If you only really want the row to be highlighted delete the coloured row in the code.
    Last edited by newdoverman; 01-22-2016 at 12:08 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  8. #8
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Highlighting the row of the current active cell [How To]

    Quote Originally Posted by newdoverman View Post
    Cool. Got one for Office 2007, though?

    The page says it's for 2010 or 2013+. It irreversibly ruins my formatting on 2007.

    In case anyone thinks it's the code, here is what happens on mine: Screen Recording

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Highlighting the row of the current active cell [How To]

    This seems to work and leave all the formatting intact.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Highlighting the row of the current active cell [How To]

    That's great, but it doesn't allow me to work in the active cell, it just selects the entire row. If I try to type something it will go in the first column, as Excel does.

    I tried mixing and matching between the two codes to show I'm willing to take initiative, but I don't know programming, so ended up with error messages.

    Is there a way to say something like this?:
    Please Login or Register  to view this content.
    Or here's another uninformed suggestion: If instead of telling it to change the formatting you tell it to do so via cell styles instead and have it add a cell style first (to highlight), and afterwards just remove it instead of having to tell it to CLEAR all formatting (destroying the original sheet), would that make things workable?

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Highlighting the row of the current active cell [How To]

    You can't change the formatting or you will wipe out the formatting of the row. That is what the first coding did that I gave you only it wiped out all the colour formatting and then formatted the row of the active cell. You can't change the formatting and then have it go back to the original. As soon as you change the formatting (the light grey) that you indicate, that is then the formatting for the row wiping out all previous formatting.

    Unless someone knows how to record the formatting for the row so that it can be restored, you're stuck.

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Highlighting the row of the current active cell [How To]

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Highlighting the row of the current active cell [How To]

    Quote Originally Posted by newdoverman View Post
    You can't change the formatting or you will wipe out the formatting of the row.
    When using cell styles, you can apply, for example, a style that adds borders to a given cell, without disturbing the other aspects of the format, like the fill, font or font color.

    If you were to incorporate this into the code, you could (by hypothesis by extension) make it, instead of modifying the fill (disturbing formatting), modifying the background pattern on select, and revert back to a blank background pattern on deselect--saving other aspects of formatting (like fill) from destruction, provided you weren't using patterns elsewhere to begin with.

  14. #14
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Highlighting the row of the current active cell [How To]

    Quote Originally Posted by shg View Post
    CODE
    Hahaaa! You're a genius! :D

    Now, it's not perfect. I can write inside a single cell. But I can't use the copy-paste function (or you'll copy the entire row), and I can't apply formatting to a single cell (it'll influence the entire row).

    A complete elimination of the problem of course would be preferred... but otherwise: Any way I can at least create a button on a toolbar somewhere to quickly enable/disable this script in case I need to perform such functions?

    This is exciting. :D
    Last edited by fluentox; 01-24-2016 at 11:57 AM.

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Highlighting the row of the current active cell [How To]

    When using cell styles, you can apply, for example, a style that adds borders to a given cell, without disturbing the other aspects of the format, like the fill, font or font color.
    If you do anything to change the environment, it will kill Undo. Seems like a very high price to pay.

  16. #16
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Highlighting the row of the current active cell [How To]

    Hm. Agreed.

    [also please see post I made directly to you at the same time as your last one, right above it]

  17. #17
    Forum Contributor
    Join Date
    09-03-2015
    Location
    IE
    MS-Off Ver
    2003 - 2016
    Posts
    258

    Re: Highlighting the row of the current active cell [How To]

    If you're not using Conditional Formatting then this might helphttp://www.ozgrid.com/forum/showthread.php?t=198354. It needs a minor change to show solid colours rather than a gradual tint.

  18. #18
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Highlighting the row of the current active cell [How To]

    Quote Originally Posted by grimes0332 View Post
    If you're not using Conditional Formatting then this might help
    I am indeed using Conditional Formatting, in at least one of my most important worksheets. I'm also not working with numerical values, which would require code-editing knowledge beyond my ability to infer alone.

    But as shg pointed out, modifying the environment robs me of the precious ability to "UNDO". I can state this also happens with the Conditional Formatting code.

    I prefer just a button to enable/disable his code. Thank you.

  19. #19
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Highlighting the row of the current active cell [How To]

    How about this way?
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  20. #20
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Highlighting the row of the current active cell [How To]

    By god you've done it... It's perfect. You didn't even need the button, by the way, it doesn't interfere were anything, not even the 'undo' function. (But I still like it).

    I just have no idea how to transfer this to my current worksheet. How do I accomplish that?

    Oh je bent Nederlands zie ik nu zelfs, wat leuk...

    EDIT: I tried copying the sheet into my workbook, copy-paste whatever code I could see into the target worksheet...
    Last edited by fluentox; 01-24-2016 at 10:14 PM. Reason: You're supposed to explain what you've tried

  21. #21
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Highlighting the row of the current active cell [How To]

    It is all done by conditional format (except the button for on/off)
    je weet toch dat nederland een deel van de wereld is

  22. #22
    Registered User
    Join Date
    01-16-2016
    Location
    Earth, Universe
    MS-Off Ver
    MS Office 2007 SP3
    Posts
    13

    Re: Highlighting the row of the current active cell [How To]

    Het blijft leuk om er weer één "in het wild" te hertreffen. (Ik weet dat dat geen officieel woord is).

    Could you give me clearer instructions on how to transfer the code to my sheet? I know nothing about VB, and don't even understand the formula functioning as the condition for the formatting.

    I could tell you in detail all I've already tried if you'd like. But I assume it'd be junk data. Tried to do it manually adapted to my sheet. It's not applying the formatting.

    I have frozen panes if that matters.

  23. #23
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Highlighting the row of the current active cell [How To]

    Show me what you have done.
    Then I can correct you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2013, 01:17 AM
  2. Making Track changes more obvious
    By asparak in forum Excel General
    Replies: 0
    Last Post: 07-12-2012, 03:23 AM
  3. Simple find function
    By jefej91 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2012, 01:15 PM
  4. Simple Function to Find Value
    By Lynks in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-04-2011, 07:29 PM
  5. Simple find with right function
    By DOR in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-02-2005, 06:20 PM
  6. [SOLVED] Simple find with right function
    By Richard Ruda in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-02-2005, 06:17 PM
  7. [SOLVED] Must Be Missing Something Obvious About ListBoxes
    By Johnny Meredith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2005, 04:05 AM
  8. Not so obvious Chart?
    By rvExcelNewTip in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-01-2005, 01:05 PM

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