Perry,

Try the macro below. Change the colorindex values to whatever you want. Select one of the list of
names and name that range "Names"

HTH,
Bernie
MS Excel MVP

Sub FindAndColorNames()
Dim c As Range
Dim d As Range
Dim myCell As Range
Dim myFindString As String
Dim firstAddress As String
Dim Colors(1 To 8) As Variant
Dim i As Integer

Colors(1) = 33
Colors(2) = 27
Colors(3) = 12
Colors(4) = 5
Colors(5) = 8
Colors(6) = 3
Colors(7) = 9
Colors(8) = 13

i = 1

For Each myCell In Range("Names")
myFindString = myCell.Value
With Cells

Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
'Then do what you want with all the cells that have been found, like
d.Offset(0, -1).Value = 0
d.Interior.ColorIndex = Colors(i)
i = i + 1

Next myCell
End Sub




"Yrrep" wrote in message ...
Have a list of 8 names in 6 columns across a sheet they are the same names but in different alpha
order.
the column before each name has a number 1 through 8 in numerical order.

I need a macro to lookup all the name (say 'Bob") then turn the number before his name to Zero,
and color his name gray


1 Pete 1 Alex
2 Bob 2 Sam
3 Alex 3 Bob
4 Sam 4 Bill
5 Bill 5 Pete

as above but continues for 8 names and 6 columns


TIA