Code behind worksheets

[Guest]

I am using Excel 97.

I have the following code which I want to put behind each worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Interior.ColorIndex = xlColorIndexNone

Range("DaysOutstanding").Interior.ColorIndex = 6
Range("Number").Interior.ColorIndex = 17

Range("A7:A100").Interior.ColorIndex = 35
Range("B7:B100").Interior.ColorIndex = 19
Range("C7:C100").Interior.ColorIndex = 34
Range("D7100").Interior.ColorIndex = 35
Range("E7:E100").Interior.ColorIndex = 34

Target.EntireRow.Interior.ColorIndex = 36

End Sub

Is there a routine that I can use to import this in so I don't have to click
on 200 worksheets, please?

 

[Ajtb]

Hi

Put your code into the workbook procedure:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Cells.Interior.ColorIndex = xlColorIndexNone

Range("DaysOutstanding").Interior.ColorIndex = 6
Range("Number").Interior.ColorIndex = 17

Range("A7:A100").Interior.ColorIndex = 35
Range("B7:B100").Interior.ColorIndex = 19
Range("C7:C100").Interior.ColorIndex = 34
Range("D7100").Interior.ColorIndex = 35
Range("E7:E100").Interior.ColorIndex = 34

Target.EntireRow.Interior.ColorIndex = 36


End Sub


Then it is only written in once yet can be applied to any number of
sheets in the workbook


HTH
Andrew Bourke

Mark wrote:
I am using Excel 97.

I have the following code which I want to put behind each worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Interior.ColorIndex = xlColorIndexNone

Range("DaysOutstanding").Interior.ColorIndex = 6
Range("Number").Interior.ColorIndex = 17

Range("A7:A100").Interior.ColorIndex = 35
Range("B7:B100").Interior.ColorIndex = 19
Range("C7:C100").Interior.ColorIndex = 34
Range("D7100").Interior.ColorIndex = 35
Range("E7:E100").Interior.ColorIndex = 34

Target.EntireRow.Interior.ColorIndex = 36

End Sub

Is there a routine that I can use to import this in so I don't have to
click
on 200 worksheets, please?

 

[Tom Ogilvy]

Use the workbook level equivalent event. It is in the ThisWorkbook Module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
SH.Cells.Interior.ColorIndex = xlColorIndexNone

SH.Range("DaysOutstanding").Interior.ColorIndex = 6
SH.Range("Number").Interior.ColorIndex = 17
SH.Range("A7:A100").Interior.ColorIndex = 35
SH.Range("B7:B100").Interior.ColorIndex = 19
SH.Range("C7:C100").Interior.ColorIndex = 34
SH.Range("D7100").Interior.ColorIndex = 35
SH.Range("E7:E100").Interior.ColorIndex = 34
Target.EntireRow.Interior.ColorIndex = 36
End Sub

Every sheet has a sheet level defined name DaysOutstanding ?