VBA Data validation with Indirect and Vlookup

[reganhayward]

Hi There

I'm an average user of Excel and VBA and have come become stuck with some code I'm trying to get working.

I have a insert rows button that inserts a new row, this includes adding in some data validation among other things.

This is fine until I try to make my formula dynamic. This part works

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(VLOOKUP($I$35,NameLookup,2,0))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "External colour"
.ErrorTitle = ""
.InputMessage = "Select the external colour you require"
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

When I try to update $I$35 in the Vlookup with a dynamic location a few columns before, the code doesn't work

' With Selection.Validation
' .Delete
' .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
' xlBetween, FormulaR1C1:="=INDIRECT(VLOOKUP(rc9,NameLookup,2,0))"
' .IgnoreBlank = True
' .InCellDropdown = True
' .InputTitle = "External colour"
' .ErrorTitle = ""
' .InputMessage = "Select the external colour you require"
' .ErrorMessage = ""
' .ShowInput = True
' .ShowError = True
' End With


Hopefully this is just a user error and is easy to fix!

Thanks!!