Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    A new Macro (XP)

    Daily, I use information from spreadsheets in my job. The sheets are very large databases, most are 150 rows X 21 columns. I have to pick specific numbers from the sheets and re-enter that info in other places, another PC, written forms, etc. I think I could reduce the time it takes me to do this if I could use a <span style="background-color: #FFFF00; color: #000000; font-weight: bold">row-highlighter</span hi>.

    How difficult would it be to create a toolbar macro that could be clicked on/off. So that when a cell is selected, the entire row (or the part of it that contains data) would highlight (shade) to a designated color (light blue, maybe).

    Example: if I selected cell D57, it would be be very useful if the entire row 57 would become shaded and then unshaded when cell C66 becomes the selection. As the newly selected C66 would highlight or shade row 66. Etc.

    I already know that the row becomes "grayed" when the row header is selected. This does me no good. First of all, it's difficult to read through the dark gray and secondly I find myself clicking from number to number throughout the database as I'm looking for the correct info I need. Taking the time to follow that trail back to the header is not a time-saver.
    - Ricky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A new Macro (XP)

    Not quite what you asked, but handy none the less: the <img src=/S/free.gif border=0 alt=free width=30 height=15> RowLiner add-in from Chip Pearson.

    And as an alternative, here is a simple macro that should go into the worksheet module for each sheet that you want to do this for:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Rows.Interior.ColorIndex = xlNone 'unshade all cells
    Rows(Target.Row).Interior.ColorIndex = 34 ' shade current row
    End Sub

    ColorIndex 34 is light blue; you can experiment with other values.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A new Macro (XP)

    Thanks, I appreciate that. Haven't played with the code yet but I did download and install the RowLiner. It's almost perfect! I set the line thickness to 3.0, the heaviest available and configured it to outline rows and columns (which is even better than what I asked for). My personal preference would've been to add shading rather than outlines but no big deal. I'll chalk this one up as a problem solved...

    Thanks, again. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A new Macro (XP)

    This seemed to work for me. Unlike Steve's earlier code, mine only resets the colors based on the previous selected cell. Also, if you name a cell "Highlight", you can put an "n" in there to disable this macro.

    <pre>Dim rLastCell
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Ucase(Range("Highlight"))="N" Then Exit Sub
    If Not IsObject(rLastCell) Then Set rLastCell = ActiveCell
    With rLastCell
    .EntireRow.Interior.ColorIndex = xlNone
    .EntireColumn.Interior.ColorIndex = xlNone
    End With
    With Target
    .EntireRow.Interior.ColorIndex = 6
    .EntireColumn.Interior.ColorIndex = 6
    End With
    Set rLastCell = Target
    End Sub</pre>


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A new Macro (XP)

    Steve <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Nice extension!

  6. #6
    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: A new Macro (XP)

    They sometimes confuse you with Jan Karel, now they confuse you with (I think) me?

    Or possibly this is in reference to some code that I had written long ago when this topic had come up before.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A new Macro (XP)

    A thousand pardons. You two look nothing alike. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A new Macro (XP)

    That's OK, Charles <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A new Macro (XP)

    Another option from the 'keep it simple' school: in eg A2:A6 format each cell with a colour and/or border and/or type style.
    If you want to format say all of row 18 with the 'highlight system' in cell A4, choose A4, click the Format Painter paintbrush, then click the row number 18 with the paintbrush.
    Done.
    Option 2: use Conditional Formatting to respond to the presence or absence of a character like H (for Highlight) in a spare column in the row concerned. Cause the conditional format to apply to all the cells you want in that row.
    Sorry to spoil the fun...

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A new Macro (XP)

    You're not spoiling the fun, but I don't think this is what Ricky intended. If I read his question correctly, he wanted to highlight the row that contains the active cell automatically.

Posting Permissions

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