Worksheet code on double click select precise range

[motilulla]

Hello,

I need a worksheet code which can select precise range double clicking on cell and deselect range if one click on any cell out of that cell.

I have attached an image in which data range is D6:H39 in the range K6:K39 I have some random numbers (these numbers says how many back rows to be selected from the current row in the columns D:H) and just to have an idea in the range L6:L39 I have shown ranges to be selected while double clicking in any cell under K6:K39......

For example...in the image if double click is done on the cell K14 (which is 14 number) so it highlight in yellow or select the range D21:H34 (14 rows back from the current row in the columns D:H)

Please see the attached image...Note: Range L6:L39 is filled just for explain the idea.

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3												Range Exapmple
4				Col C	Col D	Col E	Col F	Col G			Num	When Double Click
5				Col C	Col D	Col E	Col F	Col G			Num	Highlight Range
6				1	1	1	0	0			1	D6:H6
7				0	0	0	0	0			2	D6:H7
8				0	0	0	0	0			3	D6:H8
9				0	2	0	1	2			2	D8:H10
10				0	0	0	0	3			5	D6:H10
11				2	5	2	0	0			4	D8:H11
12				0	3	0	0	0			3	D10:H12
13				1	0	3	0	2			2	D12:H13
14				0	1	2	6	2			7	D8:H14
15				7	5	5	0	0			8	D8:H15
16				0	2	0	5	0			6	D10:H16
17				0	1	11	3	4			5	D13:H17
18				3	1	2	5	0			11	D8:H18
19				5	7	1	13	10			10	D10:H19
20				2	14	9	1	4			6	D15:H20
21				4	0	6	15	11			13	D9:H21
22				0	15	0	1	11			9	D14:H22
23				0	14	0	7	13			12	D12:H23
24				1	7	9	9	12			16	D9:H24
25				6	6	0	3	4			12	D14:H25
26				12	2	13	2	1			14	D13:H26
27				3	10	14	13	6			11	D17:H27
28				2	0	20	10	21			9	D20:H28
29				6	6	12	1	3			8	D22:H29
30				8	1	3	10	2			20	D11:H30
31				4	11	3	6	4			19	D13:H31
32				0	3	10	8	2			17	D16:H32
33				12	3	3	1	2			12	D22:H33
34				15	13	0	8	3			14	D21:H34
35				12	8	20	4	6			15	D21:H35
36				5	0	25	18	8			18	D19:H36
37				2	29	17	17	5			21	D17:H37
38				13	10	5	4	0			23	D16:H38
39				4	7	1	23	1			9	D31:H39
40
41

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Peter_SSs]

There is a certain amount of ambiguity about your columns ..

.. but see if this is headed in the right direction.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("K")) Is Nothing And IsNumeric(Target.Value) And Target.Value > 0 Then
    Cancel = True
    Target.Offset(-Target.Value + 1, -7).Resize(Target.Value, 5).Select
  End If
End Sub

 

[motilulla]

There is a certain amount of ambiguity about your columns ..
View attachment 46056
.. but see if this is headed in the right direction.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("K")) Is Nothing And IsNumeric(Target.Value) And Target.Value > 0 Then
    Cancel = True
    Target.Offset(-Target.Value + 1, -7).Resize(Target.Value, 5).Select
  End If
End Sub

Peter_SSs, sorry for the inconvenience, after inserting a 1 column to left side for adjusting the correct range I forgot to correct the columns in the header.

Applied and tested the code it worked 100% perfect.

Thank you very much for taking a time and solving my request.

Good Luck and have a good time always.

Kind Regards,
Moti

 

[Peter_SSs]

You're welcome. Glad it worked for you.