Select Cell Highlight Range

[knotty150]

Hi All

I'm looking for some help with an issue I'm having please:

I've got a table of stats; 1 row per salesperson. What I'd like is when I select a certain cell within a salesperson's row, the entire range of the particular salesperson row is highlighted i.e.:

Select Cell A1, Range A1:A10 becomes highlighted (though this rule should apply when clicking any cell within range A1:10, and also apply to the whole stats table (range A1:G10)).

Additionally, I'd like for the contents of the salesperson name (lets say there all in Column A), to populate in a different cell, dependant upon whichever row is selected i.e.:

Select Cell B5 and the contents of Cell B1 populate into cell $A$15 (basically, I can click anywhere within a salesperson row, and the salesperson name (column A) populates in a different cell so I can then perform a VLOOKUP on it.

I've got the second part working with the following worksheet code, but this only applies when I click directly on the salesperson name (column A), whereas I'd like to it apply whenever any cell is selected within the relevant salesperson row:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("AE98:AE131")) Is Nothing Then
Range("A74") = Target.Value
End If
End Sub

The idea behind this is I want to create a robust and clear user interface which displays KPI's by salesperson in the main table of stats, but then dependant upon whichever salesperson row is selected, display a separate table of stats underneath the main table which displays some more granular detail.

Hope that makes sense?

Thanks very much in advance of any assistance!

Rich

 

[ParamRay]

.
.

Perhaps you can adapt something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim SalesRng As Range
    Dim SalesMan As Range
    
    Set SalesRng = Me.Range("A1:G10")
    Set SalesMan = Me.Range("A15")
    
    SalesRng.Interior.Pattern = xlNone
    SalesMan.ClearContents
    
    If Not Intersect(Target, SalesRng) Is Nothing Then
        With Intersect(Target.EntireRow, SalesRng)
            .Rows(1).Interior.Color = RGB(255, 255, 0)
            SalesMan.Value = .Cells(1, 1).Value
        End With
    End If

End Sub