This is the code I came up with but it didnt work.. Excel complains about the name of the Private sub... Not sure if we can have 2 different private ones...if I change the name of the second excel is not complaining but only the first is working... Please help as my VBA knowledge is limited and I have spent hours on line for nothing
Private Sub Worksheet_Change(ByVal Target As Range)
' If a cell is updated in range A1:A1000 then...
If Not Intersect(Target, Range("A1:A5000")) Is Nothing Then
' If the new target value is not blank then...
If Target.Value <> "" Then
' Do a quick count and if the new target value exists in your list of PN then...
If Application.WorksheetFunction.CountIf(Sheets("Data Source").Range("A1:A5000"), Target.Value) <> 0 Then
' Do a worksheet function to VLOOKUP the corresponding Age, Sex, Country and Town details
Range("B" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E5000"), 2, False)
' Else if the name did not exist in your list of people then...
Else
' Update the detail cells with "N/A"
Range("B" & Target.Row).Value = "N/A"
End If
' Else if the target value is blank then...
Else
' Clear columns B to E of the target row
Range("B" & Target.Row).ClearContents
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' If a cell is updated in range A1:A1000 then...
If Not Intersect(Target, Range("A1:A5000")) Is Nothing Then
' If the new target value is not blank then...
If Target.Value <> "" Then
' Do a quick count and if the new target value exists in your list of PN then...
If Application.WorksheetFunction.CountIf(Sheets("Data Source").Range("B1:B5000"), Target.Value) <> 0 Then
' Do a worksheet function to VLOOKUP the corresponding Age, Sex, Country and Town details
Range("C" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Offset(0, 1).Value, Sheets("Data Source").Range("B2:E5000"), 2, False)
' Else if the name did not exist in your list of people then...
Else
' Update the detail cells with "N/A"
Range("C" & Target.Row).Value = "N/A"
End If
' Else if the target value is blank then...
Else
' Clear columns B to E of the target row
Range("C" & Target.Row).ClearContents
End If
End If
End Sub