VBA across different sheets

Hello Everyone
As can be seen with below code it works partially as supposed to. The problem is that the lookup cell is located in another sheet, how can i incorporate that into the code? Furthermore, the red color should dissapear if the "code input" cell is empty. Lastly, the red color should be shown in the name cell if the user inputs an invalid code. (see excel sheet for example)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

Application.EnableEvents = False

For Each Cell In Target
If Cell.Column = 2 Then
If WorksheetFunction.CountIf(Columns(6), Cell) > 0 Then
Cell.Offset(0, 1) = Columns(6).Find(Cell, , xlValues, xlWhole).Offset(0, 1)
Else
Cell.Offset(0, 1).Interior.ColorIndex = 3
End If
End If
Next Cell

Application.EnableEvents = True

End Sub
Credit to Mrice for the current code

Really hope someone can help!

Hello xladept the code worked perfectly! Thank you for your assistance