Temporarily highlighting related cells

[bcarpenter]

Can anyone tell me how you can select, for example, cell B5 and it
temporarily highlights in red the whole of, for example, cell D10?
The colouring needs to disappear when the cell is no longer selected.
This needs to apply to a number of cells with different references, so
when C5 is selected, cell D11 should be temporarily coloured red; cell
D5 is selected, cell D12 is highlighted.

This is to enable me to click on a network patch-panel port number and
it immediately shows to which switch port number it is connected.
This information is updated manually, so once the references are in
they should not need to be changed.

Anyone?

 

[Guest]

You could use a Worksheet.selectionchange event to do this for the worksheet
in question

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set myRange = Range("C5:C6")
If Not Intersect(Target, myRange) Is Nothing Then
Set r = Target.Offset(6, 1)
r.Interior.ColorIndex = 3
Else
Cells.Interior.ColorIndex = 2
End If

End Sub

If you select either C5 or C6 it will change the corresponding cell
background to RED. If you select any other cell, it will change the
background to WHITE

 

[Guest]

Try this corrected version

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set myRange = Range("C5:C6")
If Target.Count <> 1 Then Exit Sub
If Not Intersect(Target, myRange) Is Nothing Then
Set r = Target.Offset(6, 1)
r.Interior.ColorIndex = 3
Else
Cells.Interior.ColorIndex = xlNone
End If

End Sub