andytpl
08-14-2007, 01:54 AM
I have recently been fiddering with tab colour with VBA and got some good advice how to go about it. In one of the modification to a workbook the codes that used to work , now is giving problem.
In the code below the tab colour will change according to the value of the cells in B4 and K3. Now in cell B4 I have a data validation list which is linked to another sheet (menu) with cell reference range of A252 to A260. With this change the codes are not able to change the colour although the reference result is the same. Any suggestion to overcome this problem?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsDate(Cells(3, 11).Value) Then
ActiveSheet.Tab.ColorIndex = 1
Else
Select Case UCase(Cells(4, 2).Value)
Case "MIKE HORENKO", "M HORENKO"
ActiveSheet.Tab.ColorIndex = 39
Case "IFRON DAWKES", "I DAWKES"
ActiveSheet.Tab.ColorIndex = 10
Case "L QUINN", "LUKE QUINN"
ActiveSheet.Tab.ColorIndex = 46
Case "M MCGIFFIN", "MMCG"
ActiveSheet.Tab.ColorIndex = 35
Case "K VELLA", "KURT VELLA"
ActiveSheet.Tab.ColorIndex = 33
Case "A TAN", "ANDY TAN"
ActiveSheet.Tab.ColorIndex = 19
Case "CM LEONG", "LCM"
ActiveSheet.Tab.ColorIndex = 44
Case "MARK AVERY", "M AVERY"
ActiveSheet.Tab.ColorIndex = 34
Case ""
ActiveSheet.Tab.ColorIndex = 2
End Select
End If
End Sub
In the code below the tab colour will change according to the value of the cells in B4 and K3. Now in cell B4 I have a data validation list which is linked to another sheet (menu) with cell reference range of A252 to A260. With this change the codes are not able to change the colour although the reference result is the same. Any suggestion to overcome this problem?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsDate(Cells(3, 11).Value) Then
ActiveSheet.Tab.ColorIndex = 1
Else
Select Case UCase(Cells(4, 2).Value)
Case "MIKE HORENKO", "M HORENKO"
ActiveSheet.Tab.ColorIndex = 39
Case "IFRON DAWKES", "I DAWKES"
ActiveSheet.Tab.ColorIndex = 10
Case "L QUINN", "LUKE QUINN"
ActiveSheet.Tab.ColorIndex = 46
Case "M MCGIFFIN", "MMCG"
ActiveSheet.Tab.ColorIndex = 35
Case "K VELLA", "KURT VELLA"
ActiveSheet.Tab.ColorIndex = 33
Case "A TAN", "ANDY TAN"
ActiveSheet.Tab.ColorIndex = 19
Case "CM LEONG", "LCM"
ActiveSheet.Tab.ColorIndex = 44
Case "MARK AVERY", "M AVERY"
ActiveSheet.Tab.ColorIndex = 34
Case ""
ActiveSheet.Tab.ColorIndex = 2
End Select
End If
End Sub