Altered column highlight on double click

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Hello,</SPAN></SPAN>

Highlights different column on double clicking. </SPAN></SPAN>

For example double clicking (highlight multicolumn) on cell N12-highlihght 2 columns C12:D42, on cell O12-highlihght 4 columns C12:F42, on cell P12-highlihght 6 columns C12:H42 </SPAN></SPAN>

For example double clicking (highlight single column) on cell U12-highlihght 1 columns C12:C42, on cell AB12-highlihght 1 columns D12:D42, on cell AF12-highlihght 6 columns E12:E42</SPAN></SPAN>

Data looks as shown below</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
4
5n1n2n3n4n5n6n1,2n3,4n5,6n1n2n3
64872594 | 84 | 8 | 7 | 24 | 8 | 7 | 2 | 5 | 9487
791033829 | 109 | 10 | 3 | 39 | 10 | 3 | 3 | 8 | 29103
86002006 | 06 | 0 | 0 | 26 | 0 | 0 | 2 | 0 | 0600
96631556 | 66 | 6 | 3 | 16 | 6 | 3 | 1 | 5 | 5663
105664715 | 65 | 6 | 6 | 45 | 6 | 6 | 4 | 7 | 1566
118610278 | 68 | 6 | 1 | 08 | 6 | 1 | 0 | 2 | 7861
127364177 | 37 | 3 | 6 | 47 | 3 | 6 | 4 | 1 | 7736
1320651072 | 02 | 0 | 6 | 52 | 0 | 6 | 5 | 10 | 7206
142209762 | 22 | 2 | 0 | 92 | 2 | 0 | 9 | 7 | 6220
153241603 | 23 | 2 | 4 | 13 | 2 | 4 | 1 | 6 | 0324
162146152 | 12 | 1 | 4 | 62 | 1 | 4 | 6 | 1 | 5214
170134760 | 10 | 1 | 3 | 40 | 1 | 3 | 4 | 7 | 6013
188414128 | 48 | 4 | 1 | 48 | 4 | 1 | 4 | 1 | 2841
191872461 | 81 | 8 | 7 | 21 | 8 | 7 | 2 | 4 | 6187
209751979 | 79 | 7 | 5 | 19 | 7 | 5 | 1 | 9 | 7975
217485787 | 47 | 4 | 8 | 57 | 4 | 8 | 5 | 7 | 8748
2258411025 | 85 | 8 | 4 | 15 | 8 | 4 | 1 | 10 | 2584
23102397410 | 210 | 2 | 3 | 910 | 2 | 3 | 9 | 7 | 41023
245144985 | 15 | 1 | 4 | 45 | 1 | 4 | 4 | 9 | 8514
252813112 | 82 | 8 | 1 | 32 | 8 | 1 | 3 | 1 | 1281
263744913 | 73 | 7 | 4 | 43 | 7 | 4 | 4 | 9 | 1374
278633988 | 68 | 6 | 3 | 38 | 6 | 3 | 3 | 9 | 8863
287718377 | 77 | 7 | 1 | 87 | 7 | 1 | 8 | 3 | 7771
293483473 | 43 | 4 | 8 | 33 | 4 | 8 | 3 | 4 | 7348
309914699 | 99 | 9 | 1 | 49 | 9 | 1 | 4 | 6 | 9991
314131254 | 14 | 1 | 3 | 14 | 1 | 3 | 1 | 2 | 5413
323957793 | 93 | 9 | 5 | 73 | 9 | 5 | 7 | 7 | 9395
339346029 | 39 | 3 | 4 | 69 | 3 | 4 | 6 | 0 | 2934
340755770 | 70 | 7 | 5 | 50 | 7 | 5 | 5 | 7 | 7075
359896179 | 89 | 8 | 9 | 69 | 8 | 9 | 6 | 1 | 7989
368148958 | 18 | 1 | 4 | 88 | 1 | 4 | 8 | 9 | 5814
376876906 | 86 | 8 | 7 | 66 | 8 | 7 | 6 | 9 | 0687
3858107235 | 85 | 8 | 10 | 75 | 8 | 10 | 7 | 2 | 35810
395466275 | 45 | 4 | 6 | 65 | 4 | 6 | 6 | 2 | 7546
402346422 | 32 | 3 | 4 | 62 | 3 | 4 | 6 | 4 | 2234
414111714 | 14 | 1 | 1 | 14 | 1 | 1 | 1 | 7 | 1411
429467759 | 49 | 4 | 6 | 79 | 4 | 6 | 7 | 7 | 5946
Sheet2


Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
How about
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Cols As String
   If Not Intersect(Target, Range("N6:P42,U6:U42,AB6:AB42,AF6:AF42")) Is Nothing Then
      Select Case Target.Column
         Case 14, 21
            Cols = "C:C"
         Case 15
            Cols = "C:D"
         Case 16
            Cols = "C:E"
         Case 28
            Cols = "D:D"
         Case 32
            Cols = "E:E"
      End Select
      Intersect(Range(Cols), Rows(Target.Row & ":42")).Select
   End If
End Sub
 
Upvote 0
Hi Moti; Welcome to the Mr Excel Board. You came to the right place for your Excel Questions AND BETTER THE ANSWERS.

Question: Are the Columns you will be double-clicking on Limited or Restricted to the six (6) you provided, namely N, O, P, U, AB, AF?

Jim
 
Upvote 0
Upvote 0
Fluff said:
How about
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Cols As String
   If Not Intersect(Target, Range("N6:P42,U6:U42,AB6:AB42,AF6:AF42")) Is Nothing Then
      Select Case Target.Column
         Case 14, 21
            Cols = "C:C"
         Case 15
            Cols = "C:D"
         Case 16
            Cols = "C:E"
         Case 28
            Cols = "D:D"
         Case 32
            Cols = "E:E"
      End Select
      Intersect(Range(Cols), Rows(Target.Row & ":42")).Select
   End If
End Sub
Click to expand...
Fluff, thank you very much, code worked Perfect! And I find the code you have provided it is very easy to modified and can be adapted in various situations too. </SPAN></SPAN>

Have a nice weekend </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>:)
 
Upvote 0
jim may said:
Hi Moti; Welcome to the Mr Excel Board. You came to the right place for your Excel Questions AND BETTER THE ANSWERS.
Jim
Click to expand...
Thank you jim, :)

jim may said:
Question: Are the Columns you will be double-clicking on Limited or Restricted to the six (6) you provided, namely N, O, P, U, AB, AF?

Jim
Click to expand...
No, it is an example truly there is nearly 20+ columns but I achieved to worked with all areas modifying Fluff code easily </SPAN></SPAN>

Thank you so much for your interest. </SPAN></SPAN>

Have a nice weekend </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Upvote 0
Fluff has provided an outstanding Solution. As an AUDITOR on everything that comes my way == I have modified his code to comply with your specific request.
Correct me if I am wrong. Also, you are not restricted to Rows 6:42 for double-clicking on.. HTH

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Cols As String
   If Not Intersect(Target, Range("N:P,U:U,AB:AB,AF:AF")) Is Nothing Then
      Select Case Target.Column
         Case 14
            Cols = "C6:D"
         Case 15
            Cols = "C6:F"
         Case 16
            Cols = "C6:H"
         Case 21
            Cols = "C6:C"
         Case 28
            Cols = "D6:D"
         Case 32
            Cols = "E6:E"
      End Select
      Range(Cols & Range("C" & Rows.Count).End(xlUp).Row).Select
   End If
End Sub
 
Upvote 0
motilulla said:
Fluff, thank you very much, code worked Perfect! And I find the code you have provided it is very easy to modified and can be adapted in various situations too.

Have a nice weekend

Kind Regards,
Moti:)
Click to expand...
You're welcome & thanks for the feedback
 
Upvote 0
Fluff... Your Code is EXCELLENT.
I've been trying to learn this stuff over a 15-year period (AT A SNAIL's PACE).
One Question.

I'm still confused with the When to Use and When Not to Use the CANCEL = TRUE Code line within a lot of event-code that offers Cancel as an argument.
Why is the Cancel = True not used here in your code?

Jim
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
55
daveyc18
D
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
270
Peter_SSs
Peter_SSs
S
  • Solved
highlight duplicate values, but with a different color for each unique value
Replies
7
Views
270
dreid1011
dreid1011
E
  • Question Question
If statement
Replies
4
Views
107
xlhelp15
xlhelp15
R
  • Question Question
Multi column conditional formatting
Replies
4
Views
72
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top