Locating Matches & Highlighting Background

[Old Dog]

I am trying to set up a VBA macro that will allow the users to select
an individual from a list. Then run a macro that will highlight all the
cells in the given range("A14") that contain the individual's
name. Some of the cells will contain just the individuals name while
others will contain that individual plus another. The selection should
be highlighted upon selection of either name on a cell with two names.



A1-BOB**JACK-------B1-HARRY---------C1-HARRY**JACK-------D1-JOHN------------------E1-BOB
A2-HARRY-----------B2-JOHN----------C2-BOB----------------D2-SUE--------------------E2-HARRY
A3-SUE--------------B3-SUE**JACK----C3-JOHN----------------D3-BOB**JACK-----------E3-SUE
A4-BOB--------------B4-BOB-----------C4-HARRY-------------D4-JOHN------------------E4-JOHN
A5-----------------------------------------------------------------------------------E5JACK
A6
A7-BOB
A8

GOAL: When BOB is selected IN column E1 then the macro is run. The
macro will change the background color in cells A1, A4, B4, C2 & D3
only. While this is the long way around the only issue is that it
doesn't select anything but an exact match. Any suggestion would be
appreciated.

Sub FindTrial2()
'
' Macro recorded 8/19/2006
'
Dim Find_Name As String
Dim x As Variant
Find_Name = ActiveCell
RowCounter = 0
Range("D4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop

RowCounter = 0
Range("E4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop

RowCounter = 0
Range("F4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop

RowCounter = 0
Range("G4").Select
Do Until RowCounter = "51"
ActiveCell.Offset(1, 0).Select
If ActiveCell = Find_Name Then
With Selection.Interior
.ColorIndex = 6
End With
End If
RowCounter = RowCounter + 1
Loop
End Sub

 

[Dav]

I am not sure why you need to write a macro, this seems to be
overcomplicating the process

In a cell lets say F3 create data,validation, list from somewhere else
in the spreadsheet, this will be your dropdownlist

In cell A1 goto format, conditional formating, choose formula is and
type
=isnumber(search($F$3,A1)) and choose a colour to change the cells to
highlight them


Then just copy this format by copy, paste special, formats to the other
cells a1:d4

Regards

Dav

 

[Old Dog]

Dav,

The main reason I would like to do this in VBA code is that the
spreadsheet is used by a group of undisciplined users who very often
will blow away a whole section of a spreadsheet including the formating
using the copy and paste. If I control it thru VBA code I can have a
modicum of control. If you can help further, I would appreciate it very
much.

Thanks'

Old Dog'
Learning new tricks.