Perry,

It will fail if you have a name with no occurences. If your named range of "Names" is on the sheet
with the names, then that cannot occur.

HTH,
Bernie
MS Excel MVP


"Yrrep" wrote in message ...
Not working apears to be looping in section below. Is there a color set now?

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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