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:
Sort by date Sort by votes
jim may said:
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
Click to expand...
jim, you are very correct, and I do agree with your modification, yes I noticed too and I did modified it without pointing out to Fluff, as I said it was easy to adapt due to code is simple but outstanding to vary it as you have mentioned also. </SPAN></SPAN>

I do appreciate your modifications, thank you so much</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
jim may said:
Why is the Cancel = True not used here in your code?
Click to expand...
Because the code is selecting a different set of cells, there is no need to use cancel.
If the code was simply putting a background colour on the other cells, then I would have used Cancel
 
Upvote 0
Fluff, may I need a one more favour? In an opening post I said highlight till end row to 42 as rows get varying all the time so could you please modify the code to highlights columns C:H till data find end of the columns. </SPAN></SPAN>

The line below I have modified 42 to 10000 </SPAN></SPAN>

Code: 
If Not Intersect(Target,Range("N6:P10000,U6:U10000,AB6:AB10000,AF6:AF10000")) Is Nothing Then
</SPAN></SPAN>

Thank you
Moti
</SPAN></SPAN>
 
Last edited:
Upvote 0
How about
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Cols As String
   Dim UsdRws As Long
   If Not Intersect(Target, Range("N6:P10000,U6:U10000,AB6:AB10000,AF6:AF10000")) Is Nothing Then
      UsdRws = Range("C" & Rows.Count).End(xlUp).Row
      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 & ":" & UsdRws)).Select
   End If
End Sub
 
Upvote 0
Moti,
For what it's worth (to you).. I tightened up your project in the following revised code below. I did something that is a bit unconventional by
using the GoTo -- I think this is FROWNED upon the community, but anyway... It restricts your double-Click areas to the 6 columns.
Further it is dynamic in that it auto-adjusted for the number of rows down to the last entry in Column C at any time. Even if someone
double-clicks in a column other than the 6 - they get a message (ending the macro - allowing them to try again)...
Another thing -- we are here only SELECTING the Cells involved -- You might want to actually COLOR them -- if so more is required and
maybe you know how to do that -- but at least you have the range to color.
HTH Jim


Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Cols As String
   Cancel = True
   LR = Range("C" & Rows.Count).End(xlUp).Row
   If Not Intersect(Target, Range("N6:P" & LR)) Is Nothing Then GoTo ComeHere
   If Not Intersect(Target, Range("U6:U" & LR)) Is Nothing Then GoTo ComeHere
   If Not Intersect(Target, Range("AB6:AB" & LR)) Is Nothing Then GoTo ComeHere
   If Not Intersect(Target, Range("AF6:AF" & LR)) Is Nothing Then GoTo ComeHere
ComeHere:
      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"
         Case Else
          MsgBox "Not a Qualifying Column to Click"
         Exit Sub
      End Select


      Range(Cols & LR).Select
End Sub
 
Last edited:
Upvote 0
Jim
That code prevents the use of DoubleClick anywhere in the sheet, which means you cannot doubleclick a cell in order to edit it.
 
Upvote 0
Fluff said:
How about
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Cols As String
   Dim UsdRws As Long
   If Not Intersect(Target, Range("N6:P10000,U6:U10000,AB6:AB10000,AF6:AF10000")) Is Nothing Then
      UsdRws = Range("C" & Rows.Count).End(xlUp).Row
      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 & ":" & UsdRws)).Select
   End If
End Sub
Click to expand...
Fluff, I appreciate your help, very much kind of you for modifying the code it did the trick now code is selecting the last row, as I wanted :beerchug:</SPAN></SPAN>

Good Luck</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>:)
 
Last edited:
Upvote 0
jim may said:
Moti,
For what it's worth (to you).. I tightened up your project in the following revised code below. I did something that is a bit unconventional by
using the GoTo -- I think this is FROWNED upon the community, but anyway... It restricts your double-Click areas to the 6 columns.
Further it is dynamic in that it auto-adjusted for the number of rows down to the last entry in Column C at any time. Even if someone
double-clicks in a column other than the 6 - they get a message (ending the macro - allowing them to try again)...
Another thing -- we are here only SELECTING the Cells involved -- You might want to actually COLOR them -- if so more is required and
maybe you know how to do that -- but at least you have the range to color.
HTH Jim
Click to expand...
jim, thank you for your help</SPAN></SPAN>

Fluff, altered code in the post#14 solved my issue as I needed

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:
Upvote 0
Moti
Fluff raised the legitimate point of not being able to EDIT other cells in the Sheet, Personally I use the F2 Key for ALL Edit of Cells (I never double-click to enter the Edit Mode), but anyway...
To Allow this -- all I did was to ' comment out the two (2) lines:

'Cancel = True
'MsgBox "Not a Qualifying Column to Click"

Seems to work...

HTH Jim
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
297
Peter_SSs
Peter_SSs
S
  • Solved
highlight duplicate values, but with a different color for each unique value
Replies
7
Views
304
dreid1011
dreid1011
E
  • Question Question
If statement
Replies
4
Views
120
xlhelp15
xlhelp15

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

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