XL2000: Alternating row colors

Topic

New Reply

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

Reply | Quote

Replies

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:

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote