(Double) Click to find and highlight selection in remote column

In O3:O100 I have a series of names. On clicking on a name , I'd like to highlight each occurrence of it in F3:F300. The cells in both columns have simple text in each.

I guess it could be done by simply selecting the relevant cell or by double-clicking.

The highlight would make the font of the selection red in both O3:O100 and F3:F300. Clicking or deselecting the selection in O could toggle both columns back to dark blue.

Can someone advise some code to satisfy this?

Grateful for any help.

The code below uses Conditional Formatting to highlight the matching cells. It erases the conditional formats when you toggle it off. So if you have other Conditional Formats for columns F and O, be aware that the code will erase them too.

To install the code:

• Right-click on the sheet tab.
• Select View Code from the pop-up context menu.
• Paste the code from below in the worksheet's code module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Static PrevSelect As Range
    If PrevSelect Is Nothing Then Set PrevSelect = Range("O1")
    If Not Intersect(Range("O3:O100"), Target) Is Nothing Then
        Cancel = True
        If Target.Address <> PrevSelect.Address Then
            Set PrevSelect = Target
            With Range("F3:F300,O3:O100")
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=Target.Value
                .FormatConditions(1).Font.ColorIndex = 3
            End With
        Else
            Range("F3:F300,O3:O100").FormatConditions.Delete
            Set PrevSelect = Range("O1")
        End If
    End If
End Sub

Many Thanks. This works perfectly.