View Full Version : [SOLVED:] When I move the cursor more than one cell become highlighted
RIC63
11-21-2017, 01:11 PM
Hi everyone
taking my attached file to better undestand what i try to do :
I need to highligt the current position (I10 in the sample) and the corrispondent cells D10 and N10 pratically the cells on the same row and column ( i mean column with header N-2 ).
When i move the cursor the highlghted cells changes according to new position of the cursor; the best is that this work also if i start with current position for instance in
C5 then the cells H5 and M5 become highlighted
or in
O13 the the cells J13 and E13 become highlighted and so on
thanks to anyone that can suggest a sample of code to accomplish thi check
offthelip
11-21-2017, 05:03 PM
I modified your workbook to add this on the worksheet change event. it is not the most elegant but it might work.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)tcol = Target.Column
trow = Target.Row
arhead = Range(Cells(1, 1), Cells(1, 16))
thead = arhead(1, tcol)
For i = 1 To 30
For j = 1 To 16
With Range(Cells(i, j), Cells(i, j))
.BorderAround Weight:=xlHairline
End With
Next j
Next i
If trow < 31 Then
For i = 3 To 16
If thead = arhead(1, i) Then
With Range(Cells(trow, i), Cells(trow, i))
.BorderAround Weight:=xlMedium
End With
End If
Next i
End If
End Sub
RIC63
11-22-2017, 05:04 AM
Thanks offthelip
already in this way is ok but if I could give the color as in my example would be the maximum ;-)
Thanks again
offthelip
11-22-2017, 06:59 AM
Changing the colour gets a bit more complicated because of setting the colour back to the original colour when the selection is moved away. this would involve storing the previous colour of the selected area and the other related areas, storing the addresses of where they are and then changing the colour back to previous colour in the selection change event. All quite possible but getting even messier than the code that I wrote.
I was trying to show you what was possible.
(I10 in the sample) and cells D10 and N10
C5 then the cells H5 and M5 become highlighted
O13 the the cells J13 and E13 become highlighted and so on
What is the Criteria for which cells get highlighted?
I = D&N
C=H&M
O=J&E
p45cal
11-22-2017, 08:29 AM
A different approach; the following only selects the three cells (only 1 row), it doesn't highlight them in any other way. See attached.
Code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Selection, Range("C2:F30,H2:K30,M2:P30")) Is Nothing Then
If Target.Cells.Count = 1 Then
Application.EnableEvents = False
Cells(Target.Row, 2 + (Target.Column - 2) Mod 5).Range("A1,F1,K1").Select
Target.Activate
Application.EnableEvents = True
End If
End If
End Sub
It confines itself to working when only a single cell is selected in any of the 3 coloured ranges on your sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C2:F30,H2:K30,M2:P30")) Is Nothing Then
If Target.Count = 1 Then
Columns(3).Resize(, 14).Hidden = True
y = 2 + (Target.Column - 2) Mod 5
Range(Cells(1, y).Address & "," & Cells(1, 5 + y).Address & "," & Cells(1, 10 + y).Address).EntireColumn.Hidden = False
End If
End If
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$B$1" Then Columns(3).Resize(, 14).Hidden = False
Cancel = True
End Sub
RIC63
11-23-2017, 11:04 AM
Hi offthelip
I understand and thank you again for the help you gave me
Sincerely
riccardo
RIC63
11-23-2017, 11:09 AM
Hi SamT
the concept is to have 'connected' 3 cells that are each one on the same row and in a column with the same header (N-1 or N-2...) and to have the 3 cells more visible I wanted to set a fill color and a font color
Thank for your interest Samt
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.