PDA

View Full Version : change event



cmccabe1
06-03-2014, 12:29 PM
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