CODEWITHSUNDEEP
vbarow
Eric mansen
Eric mansen

Reputation: 41

Vba, row and column number

I'm currently working on a vba script. Is there a way to receive the row and column number when you click on a cell?

An example of what i want vba to do: 

a = select.cell.row
b = select.cell.column

Does something like this exist?

Upvotes: 0

Views: 1666

Answers (2)

brettdj
brettdj

Reputation: 55672

A little gem from Ivan Moala provides this graphically:

enter image description here

coe

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// From Ivan Moala
'// http://www.mrexcel.com/hof001.php
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors

If iColor < 0 Then
    iColor = 48
Else
    iColor = iColor + 1
End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

Cells.FormatConditions.Delete

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:="TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

'// Vertical color banding
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:="TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub

Upvotes: 1

Ahmed AbdelKhalek
Ahmed AbdelKhalek

Reputation: 192

Right-click on the worksheet then click on "View code" Paste this code there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim a as Long: a = Target.Row
    Dim b as Integer: b = Target.Column
End Sub

Upvotes: 2

Related Questions