Turning Tabs Colors on the input into a cell

[Guest]

Can someone please help with this peice of code. I am trying to change the
tab color of the active sheet to either Red, Amber or Green based on the
input into cell I5 of my worksheet. Code as follows but it is not quite
right:

Private Sub iActiveCellI5_Change()
If ActiveCell.Range("I5") = "Green" Then
Sheets("1. Banking").Select
ActiveWorkbook.Sheets("1. Banking").Tab.ColorIndex = 10
Else
If ActiveCell.Range("I5") = "Amber" Then
ActiveWorkbook.Sheets("1. Banking").Tab.ColorIndex = 3
End If
If ActiveCell.Range("I5") = "Red" Then
ActiveWorkbook.Sheets("1. Banking").Tab.ColorIndex = 45
End If
End If
End Sub

Any help would be greatly appreciated....

 

[Guest]

Your macro is basically correct. However, you should put the code into the
"Worksheet_Change" event of the worksheet which contains your "I5" cell.

I modified the code a bit. See below.

Private Sub Worksheet_Change(ByVal Target As Range)
With ThisWorkbook.Sheets("1. Banking").Tab
Select Case Range("I5")
Case "Green"
.ColorIndex = 10
Case "Amber"
.ColorIndex = 45
Case "Red"
.ColorIndex = 3
Case Else
'do nothing?
End Select
End With
End Sub

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Richard Buttrey]

Can someone please help with this peice of code. I am trying to change the
tab color of the active sheet to either Red, Amber or Green based on the
input into cell I5 of my worksheet. Code as follows but it is not quite
right:

Private Sub iActiveCellI5_Change()
If ActiveCell.Range("I5") = "Green" Then
Sheets("1. Banking").Select
ActiveWorkbook.Sheets("1. Banking").Tab.ColorIndex = 10
Else
If ActiveCell.Range("I5") = "Amber" Then
ActiveWorkbook.Sheets("1. Banking").Tab.ColorIndex = 3
End If
If ActiveCell.Range("I5") = "Red" Then
ActiveWorkbook.Sheets("1. Banking").Tab.ColorIndex = 45
End If
End If
End Sub

Any help would be greatly appreciated....

I'd be inclined to name your input cell so that you can avoid having
to make it the active cell. In the procedure below I've assumed I5 is
named "input"

Private Sub iActiveCellI5_Change()

With Worksheets("1. Banking")
Select Case Range("input")
Case Is = "Green"
.Tab.ColorIndex = 10
Case Is = "Amber"
.Tab.ColorIndex = 3
Case Is = "Red"
. Tab.ColorIndex = 3
End Select
End With

End Sub

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

Thanks Edwin,

Your macro is basically correct. However, you should put the code into the
"Worksheet_Change" event of the worksheet which contains your "I5" cell.

I modified the code a bit. See below.

Private Sub Worksheet_Change(ByVal Target As Range)
With ThisWorkbook.Sheets("1. Banking").Tab
Select Case Range("I5")
Case "Green"
.ColorIndex = 10
Case "Amber"
.ColorIndex = 45
Case "Red"
.ColorIndex = 3
Case Else
'do nothing?
End Select
End With
End Sub

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com