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
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