if
Sub Trythis()
Dim rList As String
Dim SectorType As Long
SectorType = Range("T34")
Select Case SectorType
Case Is = 1
rList = "$S$41:$S$45"
Case Is = 2
rList = "$T$41:$T$45"
End Select
With Range("s40")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
End Sub
accomplishes what you want, this will acomplish it too:
Sub M_snb()
Range("s40").Validation.Add 3, 1, , "=" & range("$R$41:$R$45").offset(,Range("T34")).address
End Sub
Eventually you will need:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$T$34" Then Range("s40").Validation.Modify 3, 1, , "=" & Range("$R$41:$R$45").Offset(, Range("T34")).Address
End Sub