Selecting one Cell highlights all others with the same data

[stirlingmw]

Morning all

I have a worksheet that contains a list of courses for 2 of my departments, Column C pertain to courses "Department A" have completed and Column G pertain to courses "Department B" have complete. There are over 450 courses detailed in each of these columns, with multiple courses of the same type in each,

i.e. Column A indicates who has completed the course and that individual could have completed the same courses as another person in this column.

In column I there is a list (no duplicates) of possible courses "Department A" can complete and in Column L a list of possible courses "Department B" could complete (no duplicates).

What I am trying to do is if i select a course in Column I (rows 4 to 149) all of the courses in Column C with the same title are highlighted and likewise if I select a course from column L (rows 4 to 149) all of the courses in Column G with the same title are selected.

Any ideas?

Thanks

Steve

 

[Akuini]

Try this:
Put the code in the code module of the sheet in question:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("I4:I149,L4:L149")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            If Target.Value = "" Then Exit Sub
            Dim c As Range, g As Range
            Set c = Range("C2", Cells(Rows.Count, "C").End(xlUp))
            Set g = Range("G2", Cells(Rows.Count, "G").End(xlUp))
            
            Application.ReplaceFormat.Interior.Color = vbYellow
            c.Interior.Color = xlNone
            g.Interior.Color = xlNone
            If Target.Column = 9 Then
                c.Replace What:=Target, Replacement:="", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=True
            ElseIf Target.Column = 12 Then
                g.Replace What:=Target, Replacement:="", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=True
            End If

            Application.ReplaceFormat.Clear
        End If
    End If
End Sub

 

[stirlingmw]

Akuini

Absolutely fantastic, works perfect. Thank you.

Steve

 

[Peter_SSs]

Here is another approach that you may wish to try. If you do happen to have any existing colour in columns C or G this code won't remove that colour.
I have used 2 helper cells, Z1:Z2 but they could be any cells and they could be hidden)on the sheet.

a. Select the whole of column C by clicking its heading label then Home ribbon tab -> Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =AND(Z$1<>"",C1=Z$1) -> Format... -> On the Fill tab select the colour you want -> OK -> OK
b. Repeat step a. but selecting the whole of column G and using this formula instead =AND(Z$2<>"",G1=Z$2)
c. Use the event code below. To implement ..

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("Z1:Z2").ClearContents
  If Selection.Cells.CountLarge = 1 Then
    If Selection.Column = 9 Or Selection.Column = 12 Then
      If Selection.row > 3 And Not IsEmpty(Selection.Value) Then
        Cells(Abs(10 - Selection.Column), "Z").Value = Selection.Value
      End If
    End If
  End If
End Sub

 

[Akuini]

Akuini

Absolutely fantastic, works perfect. Thank you.

Steve

You're welcome, glad to help, & thanks for the feedback.