Highlight some back column cells on double click

[Kishan]

Hi,</SPAN></SPAN>

I need when I double click into any cell in K:L columns highlight cells in the column C</SPAN></SPAN>
When I double click into any cell in P:R columns highlight cells in the column D</SPAN></SPAN>
When I double click into any cell in U:V columns highlight cells in the column E</SPAN></SPAN>

For example when double click on cell K11, highlights the cells C11:C66 (C11 to last cell in the column)</SPAN></SPAN>

For example when double click on cell L17, highlights the cells C17:C66 (C17 to last cell in the column)</SPAN></SPAN>

For example when double click on cell M28, highlights the cells C28:C66 (C28 to last cell in the column)</SPAN></SPAN>

Same conditions will be for the columns P:Q, highlights the column D </SPAN></SPAN>
Same conditions will be for the columns U:V, highlights the column E</SPAN></SPAN>

Example sheet</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2
3
4
5			P1	P2	P3				EM1	EM2	P1	P1	P1	EM3	EM4	P2	P2	P2	EM5	EM6	P3	P3	P3
6			X	1	1											1
7			X	X	2
8			X	1	1																1
9			2	1	1													1				1
10			1	X	1
11			X	2	X						1
12			X	1	1											1
13			1	X	X
14			1	1	1											1
15			1	X	X
16			X	1	X
17			1	1	X							1
18			1	1	1																1
19			X	1	1																1
20			1	1	1																	1
21			1	1	1
22			2	1	X
23			1	1	1																1
24			X	1	1																1
25			1	1	1																	1
26			X	1	1
27			1	2	X
28			1	1	1								1								1
29			1	1	1																1
30			1	1	1																1
31			1	1	1													1			1
32			1	X	1																1
33			2	2	1																1
34			1	X	1																	1
35			2	1	1											1
36			1	X	X
37			X	1	1																		1
38			X	2	1
39			X	1	2
40			1	1	1												1
41			X	X	X
42			2	X	2							1
43			X	X	1																	1
44			X	X	1
45			1	1	X
46			1	1	1													1			1
47			2	X	1																	1
48			1	2	1
49			X	1	X
50			X	1	1
51			X	1	X
52			X	1	1																	1
53			X	1	1
54			2	1	X						1						1
55			X	X	2
56			1	X	2
57			X	2	X
58			1	1	X
59			1	1	X
60			2	1	X								1
61			X	1	2
62			2	1	1																		1
63			1	1	1												1
64			1	X	2
65			1	X	1
66			X	X	X
67
68
69
70
71
Sheet15

Thank you in advance</SPAN></SPAN>

Using Excel 2000</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[ravisingh]

Paste the following code in the relevant worksheet module of the file and you should be good to go

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Set Target = ActiveCell
    Set DatRng = Range("C5").CurrentRegion
    LastRow = 4 + DatRng.Rows.Count
    Set DatRng = Range("C6", Cells(LastRow, 5))
    DatRng.Interior.Color = xlNone
    Col = Target.Column
    Ro = Target.Row
    
    Select Case Col
     Case 11 To 13
        ColA = 3
     Case 16 To 18
        ColA = 4
     Case 21 To 23
        ColA = 5
     Case Else
        Application.SendKeys ("{Esc}")
        Exit Sub
    End Select
    Set FormatRng = Range(Cells(Ro, ColA), Cells(LastRow, ColA))
    FormatRng.Interior.Color = RGB(255, 255, 0)
    Application.SendKeys ("{Esc}")
End Sub
[code]

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  4/6/2019  8:14:01 AM  EDT
Cancel = True
Dim ans As Long
Dim lastrow As Long
ans = Target.Row
Select Case Target.Column
    Case 11 To 13
        lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    Cells(ans, 3).Resize(lastrow - ans + 1).Interior.ColorIndex = 3
    Case 16 To 18
        lastrow = Cells(Rows.Count, "D").End(xlUp).Row
    Cells(ans, 4).Resize(lastrow - ans + 1).Interior.ColorIndex = 3
    Case 21 To 22
        lastrow = Cells(Rows.Count, "E").End(xlUp).Row
        Cells(ans, 5).Resize(lastrow - ans + 1).Interior.ColorIndex = 3
End Select
End Sub

 

[ravisingh]

Hi My Answer Is This
The code needs a line to clear the fill every time the code runs, else after each successful run a new set of cells (some overlapping) will be filled. In time the entire range could have interior.colorindex=3

 

[Kishan]

Paste the following code in the relevant worksheet module of the file and you should be good to go

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set Target = ActiveCell
Set DatRng = Range("C5").CurrentRegion
LastRow = 4 + DatRng.Rows.Count
Set DatRng = Range("C6", Cells(LastRow, 5))
DatRng.Interior.Color = xlNone
Col = Target.Column
Ro = Target.Row

Select Case Col
Case 11 To 13
ColA = 3
Case 16 To 18
ColA = 4
Case 21 To 23
ColA = 5
Case Else
Application.SendKeys ("{Esc}")
Exit Sub
End Select
Set FormatRng = Range(Cells(Ro, ColA), Cells(LastRow, ColA))
FormatRng.Interior.Color = RGB(255, 255, 0)
Application.SendKeys ("{Esc}")
End Sub
[code][/QUOTE]
[URL="https://www.mrexcel.com/forum/members/ravisingh.html"][COLOR=#0000ff][B]&#x2605 ravisingh[/B][/COLOR][/URL][COLOR=#000000], superb it worked perfect even your idea has been better then mine it highlight the target cells with yellow colour and rest with colour turquoise. [/COLOR]</SPAN></SPAN>[COLOR=#000000]

I do appreciate for your kind help[/COLOR]</SPAN></SPAN>[COLOR=#000000]

Have a nice weekend[/COLOR]</SPAN></SPAN>[COLOR=#000000]

Kind Regards,[/COLOR]</SPAN>[COLOR=#000000]
Kishan :)[/COLOR]</SPAN></SPAN>[COLOR=#000000]
[/COLOR]

 

[ravisingh]

Hi Kishan,
I am happy you liked it.
I'm not sure about the turquoise part though...
Regards
Ravi

 

[Kishan]

Hi Kishan,
I am happy you liked it.
I'm not sure about the turquoise part though...
Regards
Ravi

&#x2605 ravisingh, here is the result of you code after double click see the yellow filled with target cells and rest is filled with colour turquoise (like it is mixed of colour blue and green)</SPAN></SPAN>

Results...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2
3
4
5			P1	P2	P3				EM1	EM2	P1	P1	P1	EM3	EM4	P2	P2	P2	EM5	EM6	P3	P3	P3
6			X	1	1											1
7			X	X	2
8			X	1	1																1
9			2	1	1													1				1
10			1	X	1
11			X	2	X						1
12			X	1	1											1
13			1	X	X
14			1	1	1											1
15			1	X	X
16			X	1	X
17			1	1	X							1
18			1	1	1																1
19			X	1	1																1
20			1	1	1																	1
21			1	1	1
22			2	1	X
23			1	1	1																1
24			X	1	1																1
25			1	1	1																	1
26			X	1	1
27			1	2	X
28			1	1	1								1								1
29			1	1	1																1
30			1	1	1																1
31			1	1	1													1			1
32			1	X	1																1
33			2	2	1																1
34			1	X	1																	1
35			2	1	1											1
36			1	X	X
37			X	1	1																		1
38			X	2	1
39			X	1	2
40			1	1	1												1
41			X	X	X
42			2	X	2							1
43			X	X	1																	1
44			X	X	1
45			1	1	X
46			1	1	1													1			1
47			2	X	1																	1
48			1	2	1
49			X	1	X
50			X	1	1
51			X	1	X
52			X	1	1																	1
53			X	1	1
54			2	1	X						1						1
55			X	X	2
56			1	X	2
57			X	2	X
58			1	1	X
59			1	1	X
60			2	1	X								1
61			X	1	2
62			2	1	1																		1
63			1	1	1												1
64			1	X	2
65			1	X	1
66			X	X	X
67
68
69
70
71
Sheet15

Kind Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[ravisingh]

All the best Kishan