Cell Properties Across Multiple Worksheets

[rbg0519]

I have a quick question concerning cell properties.
I'm using Excel 6.5 under Windows XP sp3

I need to (on the back end) use code to modify cell properties (font, style, size, etc.) across multiple spreadsheets. I've managed to use the code to modify the cells on the first sheet, but am unable to recall how to make the changes a global effect across the workbook, affecting all worksheets. (I tried to set this up using a With/EndWith method, but failed)

Any help would be much appreciated.

Here is the code I currently have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim intRowNumber As Integer
Dim intColNumber As Integer
Dim intSheetNumber As Integer

'fill cells A1:E5 (Sheets 1-3) with Value's of that sheet, and cell location
For intSheetNumber = 1 To 3
For intRowNumber = 1 To 5
For intColNumber = 1 To 5
Sheets(intSheetNumber).Cells(intRowNumber, Chr(intColNumber + 64)).Value = Sheets(intSheetNumber).Name & ":$" & Chr(intColNumber + 64) & "$" & Str(intRowNumber)
Next intColNumber
Next intRowNumber
Next intSheetNumber

Range("A1:E5").Columns.AutoFit
Range("A1:E5").Font.Bold = True
Range("A1:E5").Font.Name = "Times New Roman"
Range("A1:E5").Font.Size = 12
Range("A1:e5").Font.Color = vbRed

End Sub

 

[jbeaucaire]

Maybe like so:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim intRowNumber As Integer
Dim intColNumber As Integer
Dim intSheetNumber As Integer

'fill cells A1:E5 (Sheets 1-3) with Value's of that sheet, and cell location
For intSheetNumber = 1 To 3
    With Sheets(intSheetNumber)
        For intRowNumber = 1 To 5
            For intColNumber = 1 To 5
                .Cells(intRowNumber, Chr(intColNumber + 64)).Value = .Name & ":$" & Chr(intColNumber + 64) & "$" & Str(intRowNumber)
            Next intColNumber
        Next intRowNumber
        With .Range("A1:E5")
            .Columns.AutoFit
            .Font.Bold = True
            .Font.Name = "Times New Roman"
            .Font.Size = 12
            .Font.Color = vbRed
        End With
    End With
Next intSheetNumber

End Sub

 

[rbg0519]

jbeaucaire,

Thanks for your time and response.

That worked all except autofitting the cells. Adding the With/WithEnd applied the font, size, and color but did not autofit.

 

[jbeaucaire]

Now that I think about it, Autofit wouldn't work. That is a sheet specific event.