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

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

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

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

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

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

Bernie

Now I get only one column

The names repeat every week for 12 weeks each week someone is eliminated.

I guess I tried to make it simple, What I have is around 20 columns with
names, if I could select a name and then color the selected name wherever it
is on the sheet, I could add the Zero for each week as the week arrives by
hand

Thanks
Earl


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

Earl,

The latest version that I posted checks all cells for one name, based on the activecell. Make sure
that the names are actaully the same - no extra spaces at the beginning or the end.

HTH,
Bernie
MS Excel MVP


"Yrrep" wrote in message ...
Bernie

Now I get only one column

The names repeat every week for 12 weeks each week someone is eliminated.

I guess I tried to make it simple, What I have is around 20 columns with names, if I could select
a name and then color the selected name wherever it is on the sheet, I could add the Zero for each
week as the week arrives by hand

Thanks
Earl


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