Excel - xlValidateList

Hola: Con código VBA pretendo limitar los valores a ingresar en una celda, dependiendo del valor de otra celda ubicada dos columnas a la izquierda. Los valores de las listas están en unos rangos con nombre (nom1, nom2, nom3, etc.).

El problema es que no me está funcionando: Por ejemplo cuando la celda (ubicada 2 columnas a la izquierda) contiene el valor "COMPRAS" la lista que se despliega es la correspondiente al rango 'num1' no la del rango 'num2' que es lo correcto. Ya revisé bien los rangos y sus nombres y están bien. ¿Cuál puede ser el problema?

Aquí va parte del código VBA:

If Left(ActiveCell.Address, 3) = "$R$" Then
ActiveSheet.Unprotect
If ActiveCell.Offset(0, -2).Value = "VENTAS" Then

With ActiveCell.Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="=nom1"
End With

Else

If ActiveCell.Offset(0, -2).Value = "COMPRAS" Then

With ActiveCell.Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="=nom2"
End With

Else

If ActiveCell.Offset(0, -2).Value = "LOGISTICA" Then

With ActiveCell.Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="=nom3"
End With

Muy buenas,

Según yo lo veo, por el trozo de macro que has puesto, parece que esté bien, otra cosa es que se esté ejecutando antes de que pongas activa la celda. Lo que trato de decir es que hay que asegurarse de que realmente se ejecute.
Te propongo una macro que debes adaptar a tus necesidades:

Private Sub worksheet_change(ByVal target As Range)
If target = Range("D1") Then
Select Case target.Value
Case "VENTAS"
Range("F1").Validation.Delete
Range("F1").Validation.Add xlValidateList, Formula1:="=num1"
Case "COMPRAS"
Range("F1").Validation.Delete
Range("F1").Validation.Add xlValidateList, Formula1:="=num2"
Case "LOGISTICA"
Range("F1").Validation.Delete
Range("F1").Validation.Add xlValidateList, Formula1:="=num3"
End Select
End If
End Sub

Esta macro debe de estar en la hoja, no en un módulo. Se ejecutaría cada vez que cambiaras la celda "D1".

Ya dirás si te sirve.

Un saludo. Juanjo.