Excel Ranges
 

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:D").Select
Range("D1").Activate
End If

End Sub

Excel Ranges
 

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)

"Ian Wilson" wrote in message
...
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:D").Select
Range("D1").Activate
End If

End Sub

Excel Ranges
 

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
--
Don Guillett
SalesAid Software

"Ian Wilson" wrote in message
...
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:D").Select
Range("D1").Activate
End If

End Sub

Excel Ranges
 

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

Excel Ranges
 

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

Excel Ranges
 

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

wrote in message
...
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

Excel Ranges
 

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