I'm trying to get the first row in each worksheet to be shaded and have the font bold. Right now, I am able to get the first worksheet changes made, but none of the other worksheets within the same workbook are being changed. Here is what I have written so far for creating the spreadsheet and applying a format.

Public Function WRITE_TO_EXCEL(ByVal dt As System.Data.DataTable, ByVal includeheader As Boolean, ByVal worksheet_index As Integer) As Integer


        Dim mrow As DataRow
        Dim colindex As Integer
        Dim rowindex As Integer
        Dim col As DataColumn
        Dim PefRange As Excel.Range

        Try

            WRITE_TO_EXCEL = 0

            If worksheet_index = 1 Then
                objxl = New Excel.Application

                objxl.Visible = False

                objwbs = objxl.Workbooks
                objwb = objwbs.Add

                With objwb
                    .Sheets.Add()
                End With

            End If

            objws = CType(objwb.Worksheets(worksheet_index), Excel.Worksheet)

            objws.Name = dt.TableName

            With objws

                .Cells.NumberFormat = "@"

            End With

            If includeheader Then
                For Each col In dt.Columns
                    colindex += 1
                    objws.Cells(1, colindex) = col.ColumnName

                Next
                rowindex = 1
            Else
                rowindex = 0
            End If

            For Each mrow In dt.Rows
                rowindex += 1
                colindex = 0
                For Each col In dt.Columns
                    colindex += 1
                    objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()

                    'APPLY FORMATTING
                    PefRange = objwb.Application.Range("A1:AA1")

                    With PefRange.EntireRow
                        .Font.Bold = True
                        .Font.Size = 12
                        .Interior.ColorIndex = 6
                        .Interior.Pattern = Excel.XlPattern.xlPatternSolid
                        .Font.Name = "Rockwell"
                        .Font.Underline = True

                    End With

                Next

            Next

            With objws

                .Cells.EntireColumn.AutoFit()

            End With

            objxl.DisplayAlerts = False

        Catch ex As Exception

            objwb.Close()
            End


        End Try

    End Function

If you want to do something to each worksheet then the typical method is to enumerate over the collection as follows:

For Each sheet As Excel.WorkSheet In onjwb(1).WorkSheets
    'apply formatting
Next
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.