Small VBA Code

[Dharmesh Thakkar]

Dear Sir,
I have data as per below
Sheet1
Cell C2 :- Sr No
Cell E2 :- Number
----------------
Sheet2
Column-A Column-D
Sr No Number
--------------------
I would be grateful, if could help and let me know VBA code for below
If, I update C2 than E2 update automatically or
If, I update E2 than C2 update automatically
Many thanks for help

 

[Sektor]

Update to what?

 

[Weaver]

If I've understood you correctly, this might work.

Right click on the tab for sheet1, select 'view code' and paste the following code. note, if your sheets aren't called sheet1 and sheet2 then you'll have to change the code to suit.

The way it works right now is if it can't find a match it'll update the cell to a blank, just so you know there was no hit. You can change this response if you need to

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim fc, sh, tv
    If Not Intersect(Target, Range("$C$2,$E$2")) Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo reEnable
        Set sh = Sheets("Sheet2")
        tv = Target.Value
        Select Case Target.Address
            Case "$C$2"
                Set fc = sh.Columns(1).Find(what:=tv, lookat:=xlWhole)
                If fc Is Nothing Then
                    Range("E2").Value = ""
                Else
                    Range("E2").Value = fc.Offset(, 3).Value
                End If
            Case "$E$2"
                Set fc = sh.Columns(4).Find(what:=tv, lookat:=xlWhole)
                If fc Is Nothing Then
                    Range("C2").Value = ""
                Else
                    Range("C2").Value = fc.Offset(, -3).Value
                End If
        End Select
reEnable:
        Set fc = Cells.Find(what:="")
        Application.EnableEvents = True
    End If
End Sub