Automatically lookup and set an empty cell's value if adjacent cell is typed & vice versa

2 Sheets: "Database" and "Client Codes"
On Sheet Database,

Cell B4 is where one would type a Code Number.

Cell C4 is where one would type the corresponding Client Name.

On Sheet Client Codes,

Column A2 and down are Code Numbers.

Column B2 and down are corresponding Client Names.

I am hoping to make a Worksheet_Change that looks at Sheet Database Cells B4 and C4 and uses a Index match function to lookup the corresponding Number/Name from Sheet Client Codes.

I started with a Sub button to simply see if I could make it work. The Index Match formula is returning a 1004 error. The next step would be to make it a Worksheet_Change private sub that makes things simple for someone entering data.

Sub Auto_Fill()
Dim lastRow, CodeToClient As Long, ClientToCode As Long
lastRow = Worksheets("Client Codes").Cells(Rows.Count, 1).End(xlUp).Row

With Application.WorksheetFunction
CodeToClient = .Index(Sheets("Client Codes").Range("A2:A" & lastRow), _
                          .Match(Sheets("Database").Range("B4").Value, Sheets("Client Codes").Range("B2:B" & lastRow)))
ClientToCode = .Index(Sheets("Client Codes").Range("B2:B" & lastRow), _
                          .Match(Sheets("Database").Range("C4").Value, Sheets("Client Codes").Range("A2:A" & lastRow)))
End With

If Range("B4").Value <> "" Then
    If Range("C4").Value <> "" Then
        MsgBox "B4 <> blank, C4 <> blank, Exit Sub"
    Else
        MsgBox "B4 <> blank, C4 = blank," & vbNewLine _
            & "Do C4 = CodeToClient.Value" 'B4 value dictates C4
    End If
Else
    If Range("C4").Value <> "" Then
        MsgBox "B4 = blank, C4 <> blank," & vbNewLine _
        & "Do B4 = ClientToCode.Value" 'C4 value dictates B4
    Else
        MsgBox "B4 = blank, C4 = blank, Exit Sub"
    End If
End If

End Sub

Thanks for taking the time to look,
Red

Originally Posted by walruseggman

Need to rewrite.

Thanks I'll try :/

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Range
If Not Intersect(Target, Range("B4:C4")) Is Nothing Then
    Select Case Target.Column
        Case Is = 2
            With Sheets("Client Codes")
                Set x = .Columns(1).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not x Is Nothing Then
                    Target.Offset(, 1) = x.Offset(, 1)
                End If
                Set x = Nothing
            End With
        Case Is = 3
            With Sheets("Client Codes")
                Set x = .Columns(2).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not x Is Nothing Then
                    Target.Offset(, -1) = x.Offset(, -1)
                End If
                Set x = Nothing
            End With
    End Select
End If
End Sub

The sheet is a little sensitive and making a dummy one would take a considerable amount of time but thank you guys for your assistance thus far!
John I think nailed it, ditching the INDEX MATCH entirely and just going for a find. It exceeds my expectations :D
Definitely a lot easier to comprehend than making the worksheet formulas work.
Best,
Red