Turning Tabs Colors on the input into a cell

J

Jurrasicway

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....
 
E

Edwin Tam

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
 
R

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
__________________________
 
J

Jurrasicway

Thanks Edwin,

Edwin Tam said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top