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


            WRITE_TO_EXCEL = 0

            If worksheet_index = 1 Then
                objxl = New Excel.Application

                objxl.Visible = False

                objwbs = objxl.Workbooks
                objwb = objwbs.Add

                With objwb
                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

                rowindex = 1
                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



            With objws


            End With

            objxl.DisplayAlerts = False

        Catch ex As Exception


        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
Be a part of the DaniWeb community

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