I am currently using the following code to change the cell color, I have five
sheets in the workbook (they all have the same color with this code), What
would be the code to be able to set each sheet to have its own color?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
End Sub

Thanks,

Bob

Option Base 1
Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)

Static OldCell As Range

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If

Target.Interior.ColorIndex = Array(8, 7, 6, 5, 4, 3, 2, 1)(Sh.Index)
Set OldCell = Target
End Sub

HTH
--
AP

"Bob" a écrit dans le message de news:
...
I am currently using the following code to change the cell color, I have
five
sheets in the workbook (they all have the same color with this code), What
would be the code to be able to set each sheet to have its own color?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
End Sub

Thanks,

Bob

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Select Case sh.Name
Case "Sheet1"
Target.Interior.ColorIndex = 6
Case "Sheet2"
Target.Interior.ColorIndex = 10
Case "Sheet3"
Target.Interior.ColorIndex = 4
Case "Sheet4"
Target.Interior.ColorIndex = 7
Case "Sheet5"
Target.interior.ColorIndex = 12
Case Else
End Select

Set OldCell = Target
End Sub

--
Regards,
Tom Ogilvy

"Bob" wrote:

I am currently using the following code to change the cell color, I have five
sheets in the workbook (they all have the same color with this code), What
would be the code to be able to set each sheet to have its own color?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
End Sub

Thanks,

Bob

Work Great,
Thanks

"Tom Ogilvy" wrote:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Select Case sh.Name
Case "Sheet1"
Target.Interior.ColorIndex = 6
Case "Sheet2"
Target.Interior.ColorIndex = 10
Case "Sheet3"
Target.Interior.ColorIndex = 4
Case "Sheet4"
Target.Interior.ColorIndex = 7
Case "Sheet5"
Target.interior.ColorIndex = 12
Case Else
End Select

Set OldCell = Target
End Sub

--
Regards,
Tom Ogilvy

"Bob" wrote:

I am currently using the following code to change the cell color, I have five
sheets in the workbook (they all have the same color with this code), What
would be the code to be able to set each sheet to have its own color?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
End Sub

Thanks,

Bob