Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Is there a way to yellow highlight the full line in a spreadsheet, but only of the cells with values of the current line that the pointer / highlighted cell is on. And then when you move the pointer to a new cell someplace else have the highlight switch to this now current row.?? I have attached a sample spreadsheet showing what I would like the end result to be. Thanks
    Thanks
    Jerome
    Attached Files Attached Files
    • File Type: xls c.xls (22.5 KB, 13 views)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Try these threads for ideas/techniques:
    <post#=36752>post 36752</post#>
    <post#=64090>post 64090</post#>

    Chip Pearson shows a way to highlight the active cell which could be modified
    http://www.cpearson.com/excel/excelM...ightActiveCell

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Here is a way to do this, but it does require using column IV and you could not use it for anything else.

    First, do the following:

    1- Select all of the cells where you want this formatting to occur (the entire worksheet if that is what you want).

    2- From the Format menu click on "Contitional Formatting..."

    3- In the dialog box click on the arrow in the drop down list and choose "Formula is".

    4- In the text box for the formula enter:

    <pre>=AND(A1<>"",$IV1<>"")
    </pre>


    If cell A1 is not the active cell in the cells you selected in step 1, replace A1 with the cell that is the active cell, and replace the row number in the $IV1 with the row number of the active row.

    5- Click on the Format button, then on the Patterns tab anc click on the color you want the cells formatted with.

    6- Click on OK to close all dialog boxes.

    Now, right click on the worksheet tab and select "View Code" from the pop up menu. Click on the arrow in the left hand drop down list in the VBE window and select "Worksheet". If the right hand drop down list does not display "SelectionChange" (it should), click on the arrow and select "SelectionChange". Replace the dummy SelectionChange routine in the VBE edit window with the following:

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Range("IV1").EntireColumn.ClearContent s
    ActiveSheet.Range("IV1").Offset(ActiveCell.Row - 1, 0).Value = "X"
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Legare: I had to wait till the night to work on it and::
    Works like a charm
    many many thanks
    just perfect
    Jerome

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Legare:
    Works really well, but every movement causes the whole screen to redraw ie flash. The flickering is a little bothersome. Any suggestions.
    Thanks
    JErome

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Unfortunately, I have already done everything possible to minimize the flashing by using Application.ScreenUpdating=False. It is possible to eliminate the flash when the movement is horizontal in the same row by changing the Selection Change event routine as below:

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Range("IV" & Target.Row) <> "" Then Exit Sub
    Application.ScreenUpdating = False
    ActiveSheet.Range("IV1").EntireColumn.ClearContent s
    ActiveSheet.Range("IV1").Offset(ActiveCell.Row - 1, 0).Value = "X"
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Many thanks, works better, Too bad can't do the trade off the other way ie no blinking vertical and blinking horizontal
    Jerome

  8. #8
    New Lounger
    Join Date
    Oct 2002
    Location
    Monterey, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Legare,
    Great trick. The only problem is that once it is implemented one can no longer copy a cell let's say from from c15 to c16 because as soon as you click "arrow down" the "selected" cell (copied) is no longer selected.
    Do you have a way around this.
    Thank you.

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    What a challenge! Excel fought me tooth and nail, but I think that I won! Make a copy of your workbook and get rid of all of Legare's stuff. Keep the original in case I misunderstood the question. Right-click on the sheet tab where you want highlighting and select view code. Paste the code below into the code module and try it out. HTH --Sam
    <pre>Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static rngCopied As Range
    Static rngLast As Range
    Dim c As Range, savMode As Long, lngError As Long

    'Initialize rngLast whenever it is deleted or is nothing
    On Error Resume Next
    lngError = rngLast.Row
    If Err Then Set rngLast = Target
    On Error GoTo 0

    savMode = Application.CutCopyMode
    If savMode <> xlCopy Then Set rngCopied = Nothing
    If Target.Row <> rngLast.Row Then
    rngLast.EntireRow.Interior.ColorIndex = xlNone
    For Each c In Target.EntireRow.Cells
    On Error Resume Next ' in case of #Errors
    If c <> "" Then c.Interior.ColorIndex = 6
    On Error GoTo 0
    Next c
    If savMode = xlCopy And rngCopied Is Nothing Then _
    Set rngCopied = rngLast
    If savMode = xlCopy Then
    rngCopied.Copy
    Else
    Application.CutCopyMode = savMode
    Set rngCopied = Nothing
    End If
    End If
    Set rngLast = Target
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    I hadn't noticed that problem. Here is some code that seems to fix that. Replace the previous Selection Change event routine with this:

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Range("IV1").Value = Target.Row Then Exit Sub
    Application.ScreenUpdating = False
    ActiveSheet.Range("IV1").Value = Target.Row
    Application.ScreenUpdating = True
    End Sub
    </pre>


    Then replace the Conditional Format formula with this:

    <pre>=AND(A1<>"",ROW(A1)=$IV$1)
    </pre>

    Legare Coleman

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    <P ID="edit" class=small>(Edited by jrklein on 31-Jan-03 00:21. Mild GLITCH on Saving and Reopening)</P>Dear Sam:
    Unbelieveable impressive. Looks like BIll Gates coded it himself. Not to minimize Legere's work and kindness, this expands to the number of cells in a given row that have information and contract,s. No screen flickering. Just unbelievable perfect and very helpful to reading a complex spreadsheet.
    Many many thanks. You name of brainbench is well earned. But again thanks for Legere's kindness in tackling this. ONE GLITCH: If you have multiple sheets in a worksheet and Save the Worksheet and Close the file, And Then REOPEN the file, you get a double yellow line (original line prior to close from last save and now where you put the new active cell), the yellow line shows up on the latest sheet and if you then move the active cell the original yellow line remains until you move the active cell through it. Not a big deal, just detracts from the elegance of your solution.SEE ATTACHMENT
    Regards
    Jerome
    Attached Images Attached Images
    • File Type: jpg b.jpg (4.5 KB, 3 views)

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Hi Legare,

    This can be made much simpler!

    - select the cells to get this behaviour
    - Format, conditional format (assuming cell A1 is the active cell)
    - Formula Is:
    =AND(ROW(A1)=ROW(INDIRECT(CELL("address"))),A1<>"" )
    - choose the format.
    - In the sheet's code module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    End Sub

    Finished.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    That is by far the best way to do this!
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Try Jan Karel Pieterse's method, it is much better than mine or Sammy's.
    Legare Coleman

  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Yellow Highlight of current line in Excel (Excel 2002 / XP)

    Legare:
    With all due respect, Sam's solution is much faster and cleaner it expands and contacts as needed and does not slow the screen down, Jan's solution slows the worksheet down considerably. Again thank you for all your considerable efforts and it is nice to see that this has appeal to others
    Sam;s approach just needs to somehow remove the code on closing.
    Jerome

Page 1 of 2 12 LastLast

Posting Permissions

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