Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Highlighting Active.Row

  1. #1
    New Member
    Join Date
    Dec 2014
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Highlighting Active.Row

    Hey.

    Trying to get this solved but strangely it don't work.
    I am using Excel 2013.

    Ok, i've found 2 solutions so far.

    The first one (which is not good for me)
    is easy, but the problem with it is that it removes all other colours from the worksheet when working, i've came along some posts where you can actually store your formatting on a seperate sheet, but no this aint gonna be my solution.


    The second one.
    Looks neat, could use it, it just don't work
    https://youtu.be/YSRuGR3q4uw?t=3m35s

    Here's what i've done:
    -Created new name in name manager: ActiveRow then made it refer to =1
    -Applied conditional formatting to the whole sheet: =ROW(A1)=activerow
    -Then added the code to Sheet 1:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    With ThisWorkbook.Names("activerow")
        .Name = "activerow"
        .RefersToR1C1 = "=" & ActiveCell.Row
    End With
    
    End Sub

    When i click around the sheet i notice some loading, but the highlighting won't happen.
    Could you take a look at my issue please? Any other solutions are welcome.

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,380
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Highlighting Active.Row

    I'm using these in Tandem
    Code:
    Public Sub highlightFormaulas()
        With Sheets("data").Range("C:AQ")
            'ActiveSheet.UsedRange
            .SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 35    '43 '6
        End With
    End Sub
    
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub
        Application.ScreenUpdating = False
        ' Clear the color of all the cells
        Cells.Interior.ColorIndex = 0
        With Target
            ' Highlight the entire row and column that contain the active cell
            '.EntireRow.Interior.ColorIndex = 6 '8
            .EntireRow.Interior.ColorIndex = 6    '8
            '.EntireColumn.Interior.ColorIndex = 8
        End With
        Application.ScreenUpdating = True
        highlightFormaulas
    End Sub
    as you say it will change any manually applied highlight, but if they are applied with CF then when you change focus the other highlights return
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    Dec 2014
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlighting Active.Row

    Hey mole.

    Thanks for your reply
    Unfortunately your code gives me "No cells were found" error
    It works a bit, so i can see that it removes all other highlighting. This is what i'm trying to avoid.
    The code i've pasted would do this, if it could work. Any chance you see what's the problem with it?

    *EDIT
    My sheet is very colorfull, and wouldn't want to re apply those formatting.
    Also trying to find the best code, which takes the less performance.
    My 2. code if it could be solved, would be great
    Last edited by Viktor86HUN; Apr 28th, 2015 at 04:17 AM.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Highlighting Active.Row

    Quote Originally Posted by Viktor86HUN View Post
    *EDIT
    My sheet is very colorfull, and wouldn't want to re apply those formatting.
    Also trying to find the best code, which takes the less performance.
    My 2. code if it could be solved, would be great
    It depends how colorful your sheet is as to whether this will work for you or not. What the procedure does is color only the uncolored cells in the active row yellow. so if you do not have a lot of uncolored cells, you will not be able to see the highlight very well. Anyway, this workbook (not worksheet) event code, so find the item labeled "ThisWorkbook" in the VB editor's Project list window and double click it to open its code module, then copy/paste this code into it...

    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      Application.FindFormat.Clear
      Application.ReplaceFormat.Clear
      Application.FindFormat.Interior.Color = vbYellow
      Application.ReplaceFormat.Interior.Color = xlNone
      Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
      Application.FindFormat.Clear
      Application.ReplaceFormat.Clear
      Application.FindFormat.Interior.Color = xlNone
      Application.ReplaceFormat.Interior.Color = vbYellow
      Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
      Application.FindFormat.Clear
      Application.ReplaceFormat.Clear
    End Sub
    That's it, go to any worksheet and click a cell.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,380
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Highlighting Active.Row

    Quote Originally Posted by Viktor86HUN View Post
    Hey mole.

    Thanks for your reply
    Unfortunately your code gives me "No cells were found" error
    should have been installed on the worksheet, its triggered by clicking around the target sheet only, so shouldn't be able to say it found nothing. This was on the back of sheet called "data"
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  6. #6
    New Member
    Join Date
    Dec 2014
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlighting Active.Row

    Mr Rothstein. That code is great, makes exactly what i want.
    Only problem with it, is it's performance.
    Which i managed to solve my disabling Application.Screenupdating.
    Now works like a charm.
    Thank you Roth, you made my day

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Highlighting Active.Row

    Quote Originally Posted by Viktor86HUN View Post
    Only problem with it, is it's performance.
    Which i managed to solve my disabling Application.Screenupdating.
    Now works like a charm.
    I guess that is because your sheet has a lot of cells filled in. I never really tested it with a crowded worksheet, so thanks for the feedback on that... I will modify my archive of that function to include turning off screen updating.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Dec 2014
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlighting Active.Row

    Is there a way to apply this function to a checkbox?
    if it check the function could work, but would be nice to have the feature to disable it
    Im not very familiar with checkboxes and stuff

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Highlighting Active.Row

    Quote Originally Posted by Viktor86HUN View Post
    Is there a way to apply this function to a checkbox?
    if it check the function could work, but would be nice to have the feature to disable it
    Im not very familiar with checkboxes and stuff
    Where would the checkbox be located at? The way the event code is setup, it works on every sheet of the workbook... is that what you need or did you want it restricted to a single worksheet (which would then, obviously, house the checkbox)?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular
    Join Date
    Feb 2011
    Location
    Singapore
    Posts
    406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlighting Active.Row

    This is great stuff! I used to have an addin a few years ago where I found this very helpful, but it also always cleared the previously applied formatting so I seldom used it. This is a treat!

Some videos you may like

User Tag List

Tags for this Thread

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
  •