When I move the cursor more than one cell become highlighted

[RIC63]

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]

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]

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]

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.

[SamT]

(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]

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.

[snb]

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]

Hi offthelip

I understand and thank you again for the help you gave me


Sincerely
riccardo

[RIC63]

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