Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    May 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    current row /col shading (Excel 2002)

    Hi, does anyone know if it is possible to shade the current row and column to make it easier to input data? I have names on the left of the screen and am inputting numbers on the right so it is easy to make a mistake and input data in the wrong row.

    I know excel highlights the rol and col numbers as the cursor is moved, but this is not strong enough. I'd prefer the whole row to be highlighted as I move.

    Any thoughts? Cheers, Mike C.

  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: current row /col shading (Excel 2002)

    No built in way:

    Try this <img src=/S/free.gif border=0 alt=free width=30 height=15> addin from Chip Pearson

    Steve

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

    Re: current row /col shading (Excel 2002)

    You could also try: <!post=this code,307217>this code<!/post>

  4. #4
    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: current row /col shading (Excel 2002)

    I couldn't find the post so couldn't reference it. Thanks.

    One thing to be aware of with your code, is that you "erase" any cell formatting that is currently in the cell. Chip's method will not.

    Steve

  5. #5
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: current row /col shading (Excel 2002)

    Try... Data Menu | Form

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: current row /col shading (Excel 2002)

    Another possiblity is to colour alternate rows. The following macro does this (most of the code is to avoid losing the cell grey borders):

    Sub AltRowsGreenGreyBorders()
    '
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=MOD(ROW(),2)=1"
    Selection.FormatConditions(1).Interior.ColorIndex = 35
    '
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 15
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 15
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 15
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 15
    End With
    If Selection.Columns.Count > 1 Then
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 15
    End With
    End If
    If Selection.Rows.Count > 1 Then
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 15
    End With
    End If

    End Sub

    The following macro clears the colouring:

    Sub ClearFormats()
    '
    Selection.ClearFormats
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: current row /col shading (Excel 2002)

    Mike

    Another possible way to approach this:
    Since some of the difficulty is trying to match cells on the right of the screen with cells on the left, you may want to TEMPORARILY HIDE the intervening columns - Format | Columns | Hide
    After entering data then UNHIDE the subject columns.
    Simple macros can also do this.
    This is a low-tech method but sometimes that's the best.

  8. #8
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: current row /col shading (Excel 2002)

    Mike:

    Another 'low-tech' solution is to keep the names column on the screen when scrolling right, thus letting you move the numbers column(s) next to the names.

    Click in the first cell of the column to the right of the names column and select Window/Freeze Panes.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

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