Excel Ranges

[Ian Wilson]

Hello Excel Experts.

How can I perameterise this code so it selects the row
and column of whichever cell I click on?

As the code stands it will only work if I click on C4 or
D13. Thanks for your help.

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)


Dim CurrRow, CurrCol

CurrRow = ActiveCell.Row ' Get Row.
CurrCol = ActiveCell.Column ' Get Column.

If CurrRow = 4 And CurrCol = 3 Then
Range("4:4,C:C").Select
Range("C1").Activate
End If

If CurrRow = 13 And CurrCol = 4 Then
Range("13:13,D").Select
Range("D1").Activate
End If

End Sub

 

[Bob Phillips]

Hi Ian,

This is how to do it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

With Target
Application.EnableEvents = False
Set rng = Union(.EntireRow, .EntireColumn)
rng.Select
Cells(1, .Column).Activate
Application.EnableEvents = True
End With

End Sub

I must admit though that I do not like the way that row 1 is selected, I
would have thought it better to activate the original target cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

With Target
Application.EnableEvents = False
Set rng = Union(.EntireRow, .EntireColumn)
rng.Select
.Activate
Application.EnableEvents = True
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

See if this idea helps. It colors the row and column

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = 0
With ActiveCell
.EntireRow.Interior.ColorIndex = 36
.EntireColumn.Interior.ColorIndex = 36
End With
End Sub

 

[Guest]

Private Sub Worksheet_SelectionChange(ByVal Target As Range

Dim CurrRow, CurrCol, A1ColRel, RCtoA
Static inUse As Boolea

If inUse Then Exit Su
inUse = Tru

CurrRow = ActiveCell.Row ' Get Row
CurrCol = ActiveCell.Column ' Get Column
A1ColRel = Application.ConvertFormula("R1" & "C" & CurrCol, xlR1C1, xlA1, xlAbsRowRelColumn
RCtoA1 = Left$(A1ColRel, InStr(2, A1ColRel, "$") - 1

Range(CurrRow & ":" & CurrRow & "," & RCtoA1 & ":" & RCtoA1).Selec
Range(RCtoA1 & "1").Activat

inUse = Fals
End Su

 

[Guest]

Thanks guys.

I used this from Bob... nice one!

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Dim rng As Range

With Target
Application.EnableEvents = False
Set rng = Union(.EntireRow, .EntireColumn)
rng.Select
.Activate
Application.EnableEvents = True
End With

End Sub

 

[Bob Phillips]

.... and I'm glad that you are using #2, and not activating row 1<G>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Yes, that would have been my next question ;-)

-----Original Message-----
.... and I'm glad that you are using #2, and not

activating row 1<G>