click on one cell to find and highlight a related cell?

J

JustSomeGuy

is there a way to have i.e. a list of people's names in one column, and a
list of company regions in another column, so that whenever you click on a
person's name in the first column, Excel highlights that person's region in
the second column?

(so the user can find out any person's Region, by clicking on their name.)
 
G

Gary''s Student

Place the following macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("B:B").ClearFormats
Target.Offset(0, 1).Interior.ColorIndex = 6
Application.EnableEvents = True
End Sub

If you select a cell in column B, then the equivalent cell in column B will
be hi-lighted.
 
Top