Earl,

You had said:

"as above but continues for 8 names and 6 columns"

so I thought you wanted to do all at once.

If you want to just select one name, then change the code to this, and run
it after selecting a single cell.

HTH,
Bernie
MS Excel MVP

Sub FindAndColorOneName()
Dim c As Range
Dim d As Range
Dim myCell As Range
Dim myFindString As String
Dim firstAddress As String
Dim Color As Variant

'Pick your color
Color = 33

myFindString = ActiveCell.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 = Color

End Sub

"Yrrep" wrote in message
...
Bernie

Ok I fixed that now it highlights all of the selected range named ranges
and 8 of the 13 names through the sheet.

what I am trying to do is select one name color it and turn the number
before it to zero.

Earl


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