Results 1 to 5 of 5
  1. #1
    bbxl
    Guest

    XL2000: Alternating row colors

    I have a user who has formatted an Excel2000 worksheet with alternating rows of color to enhance readability. However, when he runs an MSQuery and the results are returned, this formatting is thrown into disarray. I realize this is caused by the "hiding" of the rows that do not meet the criteria of the query, but is there any way to preserve the formatting? Many thanks, Brent

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

    Re: XL2000: Alternating row colors

    Unfortunately, there is no way for Excel to guess how you want the formatting to look after the rows are hidden. Therefore, there is not going to be any automatic way to get Excel to reformat the color scheme. The best you are probably going to be able to do is write a VBA procedure that will reformat the visible rows the way you want them. I am not familiar enough with MSQuery to know if there is some way to automatically trigger the VBA routine when a query is run. The following VBA code should get you started:

    <pre>Public Sub SetRowColor()
    Dim I As Integer, iNextColor As Integer
    For I = 1 To 1000
    If Sheet1.Rows(I).Hidden = False Then Exit For
    Next I
    iNextColor = 4
    For I = I To 1000
    If Sheet1.Rows(I).Hidden = False Then
    Sheet1.Rows(I).Interior.ColorIndex = iNextColor
    If iNextColor = 4 Then
    iNextColor = 6
    Else
    iNextColor = 4
    End If
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    bbxl
    Guest

    Re: XL2000: Alternating row colors

    After posting my question, I found some free time to do a little more searching and found these instructions in Excel Help.

    1. If the "External Data Range Properties" dialog box is not displayed, click a cell in the exteral data range, and then click "Data Range Properties" on the "External Data" toolbar.

    2. To preserve cell formatting that you apply, select the "Preserve cell formatting" check box under "Data formatting and layout". To preserve column widths that you set, clear the "Adjust column width" check box under "Data formatting and layout".

    3. Click OK.

    4. To refresh the external data range, click "Refresh Data" on the "External Data" toolbar.

    My thanks for everyone's assistance in this.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: XL2000: Alternating row colors

    The following macro will apply alternate shading to the current region. It uses condidtional format to achieve this, and is preserved when lines are deleted, but not when hidden (unless the lines are hidden in multiples of 2). Also note that any existing conditional formatting is removed.

    Sub AlternateLineShade()
    Dim CurSel As String
    CurSel = ActiveCell.Address
    Application.ScreenUpdating = False
    Selection.CurrentRegion.Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=MOD(ROW(),2)=0"
    Selection.FormatConditions(1).Interior.ColorIndex = 34
    Range(CurSel).Select
    Application.ScreenUpdating = True
    End Sub

    As it stands it shades even numbered rows, but to shade odd numbered rows change to "=MOD(ROW(),2)=1".

    The "=MOD(ROW(),2)=0" can be applied manually via the Conditional Formatting dialog. Just make sure the condition is set to use Formula Is rather than Value Is.

    Andrew C

  5. #5
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL2000: Alternating row colors

    But ... how do you get alternating shaded cells in the first place? I've tried to do this dozens of times with conditional formatting and have been successful but it's been very tortuious.

Posting Permissions

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