Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Shading alternate rows (Excel 2000 >)

    Heres a nifty way to make reading a large list of data easier!

    1. Select the range that you want to format
    2. Choose Format, Conditional Formatting
    3. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and enter this formula: =MOD(ROW(),2)=0.
    4. Click the Format button, select the Patterns tab, and specify a colour for the shaded rows.
    5. Click OK twice to return to your worksheet.
    The best part is that the row shading is dynamic. You'll find that the row shading persists even if you insert or delete rows within the original range.
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Shading alternate rows (Excel 2000 >)

    Thanks Rudi

    I love "Top Tips" like this.
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Shading alternate rows (Excel 2000 >)

    Hi Rudi

    Building on your top tip

    I have just recorded this macro for all to share

    Sub Alternator()

    Cells.Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=MOD(ROW(),2)=1"
    Selection.FormatConditions(1).Interior.ColorIndex = 35
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=MOD(ROW(),2)=0"
    Selection.FormatConditions(2).Interior.ColorIndex = 36
    End Sub

    Now you get alternating yellow and green rows
    Jerry

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Shading alternate rows (Excel 2000 >)

    Great....

    Here is a version that only applies the shading to the list!
    <pre>Sub Alternator()
    Dim rngList As Range
    Set rngList = Application.InputBox("Select a cell in your list!", "Identify the list!", , , , , , 8)
    rngList.CurrentRegion.Select
    With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=MOD(ROW(),2)=1"
    .FormatConditions(1).Interior.ColorIndex = 35
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=MOD(ROW(),2)=0"
    .FormatConditions(2).Interior.ColorIndex = 36
    End With
    End Sub
    </pre>

    Regards,
    Rudi

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Shading alternate rows (Excel 2000 >)

    Nice one

    By the way congrats on becoming a 5StarLounger, you have been busy over the last 9 months
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Shading alternate rows (Excel 2000 >)

    Its not about being busy, but the interaction that I enjoy on this forum! I have learned more about MS Office and VBA in these last 9 months that the last 8 years...and its been fun aswell. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

Posting Permissions

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