SOLVED

Show the Data

Copper 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 (Copper 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
1 best response

Accepted Solutions
best response confirmed by tusharkaushik (Copper 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

View solution in original post