worksheet_change not working
 

Can anyone tell me why the following Worksheet_change event isn
working?
It should be doing the following:
Look at cell B4, if it says "Specialty" then it runs macro TLdev1,
if it says "SubSpecialty" then it runs TLdev2,
else if it says "consultant" then it should run TLdev3.

The actual macros themselves takes the column range: B:P and format
the cells dependent upon the value.

At the moment, these macro are in sheet1.

Many thank for your help with this.

Scott.



Macros starts he

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
If Target.Value = "Specialty" Then
Call TLdev1
ElseIf Target.Value = "SubSpecialty" Then
Call TLdev2
Else: Target.Value = "Consultant"
Call TLdev3
End If
End If
End Sub

Sub TLdev1() 'Spec level
Dim Cell As Range
Cells.Interior.ColorIndex = xlNone
For Each Cell In Intersect(Columns("B:P"), _
Cells.SpecialCells(xlConstants, xlNumbers))
Cell.Value = Cell.Value
Select Case Cell.Value
Case Is = 250
Cell.Interior.ColorIndex = 3 'red
Case Is = 100
Cell.Interior.ColorIndex = 6 'Yellow
Case Is = 50
Cell.Interior.ColorIndex = 4 'Green
Case Else
Cell.Interior.ColorIndex = xlNone
End Select
Next Cell
End Sub

Sub TLdev2() 'Subspec level
Dim Cell As Range
Cells.Interior.ColorIndex = xlNone
For Each Cell In Intersect(Columns("B:P"), _
Cells.SpecialCells(xlConstants, xlNumbers))
Cell.Value = Cell.Value
Select Case Cell.Value
Case Is = 100
Cell.Interior.ColorIndex = 3 'red
Case Is = 50
Cell.Interior.ColorIndex = 6 'Yellow
Case Is = 25
Cell.Interior.ColorIndex = 4 'Green
Case Else
Cell.Interior.ColorIndex = xlNone
End Select
Next Cell
End Sub

Sub TLdev3() 'Consultant level
Dim Cell As Range
Cells.Interior.ColorIndex = xlNone
For Each Cell In Intersect(Columns("B:P"), _
Cells.SpecialCells(xlConstants, xlNumbers))
Cell.Value = Cell.Value
Select Case Cell.Value
Case Is = 30
Cell.Interior.ColorIndex = 3 'red
Case Is = 15
Cell.Interior.ColorIndex = 6 'Yellow
Case Is = 7
Cell.Interior.ColorIndex = 4 'Green
Case Else
Cell.Interior.ColorIndex = xlNone
End Select
Next Cell
End Su

--
scottwilson
-----------------------------------------------------------------------
scottwilsonx's Profile: http://www.excelforum.com/member.php...fo&userid=1112
View this thread: http://www.excelforum.com/showthread.php?threadid=27159