Is there a way to have code work on selected sheets?

[imback2nite]

Thank you for reading. I'm using this code and I was hoping to be able to use it on selected pages. For example, if I only wanted to use it on worksheets 1, 3 and 5. Pages 5 thru 10. Or even if the pages I want it to work had had something unique that all the other pages didn't like cell B1 had "Name" in it. Preferably I would like to be able to name the sheets like, Sheet1, Sheet3 etc. but I'll take it anyway that it will work! The reason I'm doing this is to hopefully speed the workbook along and to shrink it. It's already at 5 megabytes. Once again thank you for reading this.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim Rng1 As Range

If Target.Address = "$D$1" Then
    ActiveSheet.Name = Left(Target.Value, 35)
    Exit Sub
End If
 On Error Resume Next
    Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
    On Error GoTo 0
    If Rng1 Is Nothing Then
        Set Rng1 = Range(Target.Address)
    Else
        Set Rng1 = Union(Range(Target.Address), Rng1)
    End If
    For Each cell In Rng1
        Select Case cell.Value
        Case vbNullString
            cell.Interior.ColorIndex = xlNone
            cell.Font.Bold = False
        Case Range("AW9").Value
            cell.Interior.ColorIndex = 6
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Else
            cell.Interior.ColorIndex = xlNone
            cell.Font.Bold = False
        End Select
    Next
End Sub

 

[Kamolga]

Dim sh as Worksheet
For Each sh In Workbooks("BOOK1.XLS").Windows(1).SelectedSheets

I always consider a user can and will change the name of a tab, so in VBA I rename the sheet1 by ShData for example. That way if the user change the caption "Database" to "Data", the macro working with ShData still works.

 

[imback2nite]

Yes. You are certainly right. Point well taken. As a matter of fact I'm a guilty party also. But is there a way I can have this code run only on selected sheets?

 

[Kamolga]

But is there a way I can have this code run only on selected sheets?

Sub test()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Windows(1).SelectedSheets
 Range("A1") = "Test ok"
Next sh
End Sub

I selected two sheets (I was on sheet2 and pressed shift while selecting sheet3) out of 4 in a workbook...the macro above wrote "Test Ok" in the cell A1 of sheet 2 and Sheet 3 but not in sheet 1 and sheet 4.