SOLVED

Show the Data

%3CLINGO-SUB%20id%3D%22lingo-sub-2155623%22%20slang%3D%22en-US%22%3EShow%20the%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2155623%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20I%20want%20is%20basically%20whenever%20i%20select%20D2%20the%20same%20data%20will%20be%20shown%20in%20M3.%20When%20i%20go%20to%20D3%20the%20same%20will%20be%20shown%20in%20M3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESame%20for%20E2%20the%20same%20data%20will%20be%20shown%20in%20N3.%20Now%20when%20i%20go%20to%20E3%20the%20same%20will%20be%20shown%20in%20N3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20this%20code%20but%20the%20problem%20is%20it%20is%20showing%20the%20same%20value%20either%20of%20D2%20or%20E2%20in%20M3%20and%20N3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOption%20Explicit%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20Selection.Count%20%3D%201%20Then%3CBR%20%2F%3E'Put%20in%20your%20actual%20range%20and%20the%20cell%20where%20you%20the%20text%20to%20be%20shown%3CBR%20%2F%3EIf%20Not%20Intersect(Target%2C%20Range(%22D2%3AD43%22%2C%20%22E2%3AE43%22))%20Is%20Nothing%20Then%3CBR%20%2F%3ESelection.Copy%20Destination%3A%3DRange(%22M3%22%2C%20%22N3%22)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20if%20someone%20can%20help%20TIA.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2155623%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2155691%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20the%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2155691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F974672%22%20target%3D%22_blank%22%3E%40tusharkaushik%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Selection.CountLarge%20%3D%201%20Then%0A%20%20%20%20%20%20%20%20If%20Not%20Intersect(Target%2C%20Range(%22D2%3AD43%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22M3%22).Value%20%3D%20Target.Value%0A%20%20%20%20%20%20%20%20ElseIf%20Not%20Intersect(Target%2C%20Range(%22E2%3AE43%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22N3%22).Value%20%3D%20Target.Value%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2155743%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20the%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2155743%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20Help%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

What I want is basically whenever i select D2 the same data will be shown in M3. When i go to D3 the same will be shown in M3.

 

Same for E2 the same data will be shown in N3. Now when i go to E3 the same will be shown in N3.

 

I have this code but the problem is it is showing the same value either of D2 or E2 in M3 and N3.

 

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
'Put in your actual range and the cell where you the text to be shown
If Not Intersect(Target, Range("D2:D43", "E2:E43")) Is Nothing Then
Selection.Copy Destination:=Range("M3", "N3")
End If
End If
End Sub

 

Please if someone can help TIA.

4 Replies
Best Response confirmed by tusharkaushik (Occasional Contributor)
Solution

@tusharkaushik 

Like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge = 1 Then
        If Not Intersect(Target, Range("D2:D43")) Is Nothing Then
            Range("M3").Value = Target.Value
        ElseIf Not Intersect(Target, Range("E2:E43")) Is Nothing Then
            Range("N3").Value = Target.Value
        End If
    End If
End Sub

Thanks for the Help@Hans Vogelaar 

Can you please help on more thing that is in the same code if i want to select the D2 automatically e2 gets selected and the data will be shown with same functionality

What changes will be required to achieve that task?

@Hans Vogelaar 

@tusharkaushik 

If we do that, you won't be able to enter data in D2:D43, for the moment you select a cell in that range, the code will select the cell to the right of it.

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge = 1 Then
        If Not Intersect(Target, Range("D2:D43")) Is Nothing Then
            Target.Offset(0, 1).Select
        ElseIf Not Intersect(Target, Range("E2:E43")) Is Nothing Then
            Range("N3").Value = Target.Value
        End If
    End If
End Sub