Exclude hidden sheets from formatting
 

I'm using this sub which operates the way it should except it formats
one sheet hidden I would like left alone, I tried fixing it using the
below listed code, but I can't get it to work.

Public Sub Fix_Up_Sheets()
Dim SH As Worksheet
For Each SH In Worksheets
With SH
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
.Columns("c").ColumnWidth = 18
.Columns("b").ColumnWidth = 15.29
.Columns("e").ColumnWidth = 15.29
End With
End With
Next
End Sub
' this will exclude all hidden worksheets
Private Sub Test()
For Each ws in Worksheets
If Not ws.Visible = False Then
ws.Activate
[actions]
End If
Next ws
End Sub

Could someone please combine the two, thanks

Bart

Exclude hidden sheets from formatting
 

Try this

Public Sub Fix_Up_Sheets()
Dim SH As Worksheet
For Each SH In Worksheets
If SH.Visible = True Then 'exclude none visible
With SH
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
.Columns("c").ColumnWidth = 18
.Columns("b").ColumnWidth = 15.29
.Columns("e").ColumnWidth = 15.29
End With
End With
End If
Next
End Sub

Mike

"Bart" wrote:

I'm using this sub which operates the way it should except it formats
one sheet hidden I would like left alone, I tried fixing it using the
below listed code, but I can't get it to work.

Public Sub Fix_Up_Sheets()
Dim SH As Worksheet
For Each SH In Worksheets
With SH
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
.Columns("c").ColumnWidth = 18
.Columns("b").ColumnWidth = 15.29
.Columns("e").ColumnWidth = 15.29
End With
End With
Next
End Sub
' this will exclude all hidden worksheets
Private Sub Test()
For Each ws in Worksheets
If Not ws.Visible = False Then
ws.Activate
[actions]
End If
Next ws
End Sub

Could someone please combine the two, thanks

Bart

Exclude hidden sheets from formatting
 

On Sep 29, 11:25*am, Mike H wrote:
Try this

Public Sub Fix_Up_Sheets()
* Dim SH As Worksheet
* For Each SH In Worksheets
* If SH.Visible = True Then 'exclude none visible
* * With SH
* * * .Rows().RowHeight = 78
* * * .Rows(1).RowHeight = 15
* * * .Columns("C").Insert
* * * With .Range("A1:S1")
* * * * .Font.Bold = True
* * * * .Font.Color = vbRed
* * * * .Cells.Interior.Color = vbYellow
* * * * .Columns("c").ColumnWidth = 18
* * * * .Columns("b").ColumnWidth = 15.29
* * * * .Columns("e").ColumnWidth = 15.29
* * * End With
* * End With
End If
* Next
End Sub

Mike

"Bart" wrote:
I'm using this sub which operates the way it should except it formats
one sheet hidden I would like left alone, I tried fixing it using the
below listed code, but I can't get it to work.

Public Sub Fix_Up_Sheets()
* Dim SH As Worksheet
* For Each SH In Worksheets
* * With SH
* * * .Rows().RowHeight = 78
* * * .Rows(1).RowHeight = 15
* * * .Columns("C").Insert
* * * With .Range("A1:S1")
* * * * .Font.Bold = True
* * * * .Font.Color = vbRed
* * * * .Cells.Interior.Color = vbYellow
* * * * .Columns("c").ColumnWidth = 18
* * * * .Columns("b").ColumnWidth = 15.29
* * * * .Columns("e").ColumnWidth = 15.29
* * * End With
* * End With
* Next
End Sub
' this will exclude all hidden worksheets
Private Sub Test()
* *For Each ws in Worksheets
* * * If Not ws.Visible = False Then
* * * * *ws.Activate
* * * * *[actions]
* * * End If
* *Next ws
End Sub

Could someone please combine the two, thanks

Bart

Thanks Mike that works great.

Bart