Need help for macro

[Yrrep]

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 Deitrick]

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]

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]

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]

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]

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]

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]

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