The code below seems to do a lookup and place the value in the desired cell. The issue I am having is cell A2 is a data validation dropdown and when that value changes the other cells do not. I need some expert help with this. Thank you.

Dim LastRowNo As Long
LastRowNo = Cells(Rows.Count, "CA").End(xlUp).Row 
Worksheets("annovar").Range("B2").Value = Evaluate("=VLOOKUP(A2,CA5:CB" & LastRowNo & ",2,0)")
Worksheets("annovar").Range("C2").Value = Evaluate("=VLOOKUP(A2,CA5:CC" & LastRowNo & ",3,0)")


I have this code as my worksheet change event in the annovar sheet (where the user makes the change).

Private Sub Worksheet_Change(ByVal Target As Range)   
If IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
ActiveSheet.Range("$B$2:$F$2").AutoFilter Field:=1, Criteria1:="yes" 
End If
End Sub