Hi Vit,
Someone may have a better way of doing this and I will be interested if they
do. However, this is how I would do it.
Firstly on the worksheet when you select the range to group then go into
named ranges and name the range eg. Group1, Group2 etc.
Then in VBA you can identify those named ranges and assign them to range
variables.
From there you can calculate the column number within the range. (I have
never been able to directly return the relative column number withing a range
assigned to a variable).
You may wish to use intersect in a case statement to first establish what
range the selection is in. My example only uses one group.
Dim rngMyRng As Range
Dim firstColMyRng As Long
Dim selectionCol As Long
Dim selectColMyRng As Long
Dim isect As Object
Set rngMyRng = Range("Group1")
'following needs to be within case to establish which
'range the selection is in
Set isect = Application.Intersect(Selection, rngMyRng)
If isect Is Nothing Then
MsgBox "No ranges intersect with selection"
Exit Sub
End If
'Identify the first column number of the assigned range
firstColMyRng = rngMyRng.Cells(1, 1).Column
'Identify the column number of the selected cell/range
selectionCol = Selection.Column
'Therefore relative column number in named range is:
selectColMyRng = selectionCol - firstColMyRng + 1
Feel free to get back to me if you need more info or clarification.
