Data Validation Using Target.Value

[nalgene5622]

I am trying to get a data validation using vba. I can not seem to get the below code correct. I have posted part of the code below. I am wanting use the Match function to look at the Target Value. I am having trouble on this line "CHOOSE(MATCH(Target.Value,Categories,0),Column1,Column2)" The code language on Target.Value reads an error.
An example would be if cell A1 reads the word Red then add the data validation list Column1. Red is the first index in the Categories list.

                    With Target.Offset(0, 1).Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=CHOOSE(MATCH(Target.Value,Categories,0),Column1,Column2)"
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .InputTitle = ""
                        .ErrorTitle = "Cell Validated"
                        .InputMessage = ""
                        .ErrorMessage = "Cell validated.Select from dropdown list."
                        .ShowInput = True
                        .ShowError = True
                    End With

[davesexcel]

You may have to supply a sample workbook so somebody can see what the problem is.

[nalgene5622]

I have attached an example program. Just a note where it says Formula1:="=CHOOSE(MATCH(A2,Categories,0),Column1,Column2)" the A2 should read the target.value which is A2. When the program runs it should look at the value in Column A within the same row. Example if A15 has a value the formula would read Formula1:="=CHOOSE(MATCH(15,Categories,0),Column1,Column2)"

Program is also here to.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A2:A500")) Is Nothing Then
      Call ColumnG(Intersect(Target, Range("A2:A500")))
   End If
End Sub

Sub ColumnG(Target As Range)

                    With Target.Offset(0, 1).Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=CHOOSE(MATCH(A2,Categories,0),Column1,Column2)"
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .InputTitle = ""
                        .ErrorTitle = "Cell Validated"
                        .InputMessage = ""
                        .ErrorMessage = "Cell validated.Select from dropdown list."
                        .ShowInput = True
                        .ShowError = True
                    End With

End Sub

[nalgene5622]

Any suggestions?

[watersev]

hi nalgene5622, try this

Sub ColumnG(Target As Range)

    With Target.Offset(0, 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=CHOOSE(MATCH(" & Target.Address & ",Categories,0),Column1,Column2)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Cell Validated"
        .InputMessage = ""
        .ErrorMessage = "Cell validated.Select from dropdown list."
        .ShowInput = True
        .ShowError = True
    End With
    
    'clears cell in B column
    Target.Offset(0, 1).ClearContents

End Sub

[nalgene5622]

Watersev,

Thanks for the help that is exactly what I needed.

[watersev]

if your issue is Solved: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.