alternating row colors/numbers in Excel - VBA

Question

Hey guys, So I am working on a reporting tool in Access. It queries the local tables and creates a Excel document (through VBA) and makes an Excel file.

I use the following code to color alternative code and it works beautifully

 For a = 1 To rs.RecordCount
    With ExcelSheet          
        .Cells(a + 1, 1) = a 
        .Cells(a + 1, 1).EntireRow.Interior.ColorIndex = IIf((a + 1) Mod 2 = 0, 2, 15)
    End With
Next

Note I have to do a + 1 because a = 1 is the title row, and that is the title row.

Note: .Cells(a + 1, 1) = 1 numbers the rows (1 , 2, 3, ...)

Note : IIf((a + 1) Mod 2 = 0, 2, 15) The 2 and 15 are color codes.

Now my question is that when someone gets the Excel report they might delete a row, or do a sort operation or whatever and when they do that, it messes up the rows.

ex:

1  white row
2  grey row
3  white row 
4  grey row

if i sort them I get

3  white row
1  white row 
2  grey row 
4  grey row 

which is not what I want, I want it to keep the formatting and the numbering Anyone to accomplish this using VBA in Access?

Tech: Office 2007

Answer 1

This can be accomplished with the ROW() function and some conditional formatting. The ROW() function returns the current row of the cell it is in, so it will change whenever cells are deleted, moved or sorted. Conditional formatting is re-applied whenever its conditions change, so moving or sorting rows would inform Excel to update the colors accordingly. The code would look as follows:

Dim a As Integer
Dim oneRow As Range

For a = 1 To rs.RecordCount 
    With ExcelSheet

        ''// show the row number in the first cell
        .Cells(a + 1, 1).Formula = "=ROW()"

        ''// set formatting to alternate row colors
        Set oneRow = .Cells(a + 1, 1).EntireRow
        oneRow.FormatConditions.Delete
        oneRow.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=MOD(ROW()-1, 2)=0"
        oneRow.FormatConditions(1).Interior.ColorIndex = 2
        oneRow.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=MOD(ROW()-1,2)=1"
        oneRow.FormatConditions(2).Interior.ColorIndex = 15

    End With
Next a

Answer 2

@e.James

Dim rowRange As Range


ExcelSheet.Cells(1, 1).EntireColumn.ColumnWidth = 4
ExcelSheet.Cells(1, 1) = "#"
Set rowRange = Range("2:2", rs.RecordCount & ":" & rs.RecordCount)
rowRange.Select
With ExcelApp.Selection
    .FormatConditions.Delete
    .FormatConditions.Add xlExpression, Formula1:="=MOD(ROW(),2)"
    .FormatConditions(1).Interior.ColorIndex = 15
End With

That dosnt work. It just highlights the very top row (the title row) grey.

EDIT NEVERMIND Its supposed to be

Set rowRange = ExcelSheet.Range("2:2", rs.RecordCount & ":" & rs.RecordCount)

EDIT NUMBER 2: Do you know how I can insert the row numbers in each row using this method?

ANSWER:

ExcelSheet.Cells(1, 1).EntireColumn.ColumnWidth = 4
Set RowRange = Range("2:2", rs.RecordCount & ":" & rs.RecordCount)
RowRange.Columns(1).Formula = "=ROW()-1"
With RowRange
    .FormatConditions.Delete
    .FormatConditions.Add xlExpression, Formula1:="=MOD(ROW(),2)"
    .FormatConditions(1).Interior.ColorIndex = 15
End With