i have 2 sheets, sheet1 contains the sales made by many salesmen(one of the
column is "names"), sheet2 contains a list of the names of the salesmen.

i need to create a macro that will read all the names in sheet2 and compare
it against the "name" column in sheet1, and then i want the macro to change
the colour of all the of the whole row if a name from sheet2 matches a name
in the "name" column in sheet1

i am very new to coding macros so what every help u can provide me with will
be greatly appriciated :-)

This should do the trick for you Robin. This code assumes that the list of
sales people in each sheet are in Col. A. If not, you will have to change it
or just let me know.

Sub FindSalesman()

Dim cell As Range
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim SalesmanList As Range
Dim SalesMade As Range
Dim A As Variant

Application.ScreenUpdating = False

With Sheets("Sheet1")
LastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
Set SalesMade = Range(.Cells(1, "A"), .Cells(LastRow1, "A"))
End With

With Sheets("Sheet2")
LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
Set SalesmanList = Range(.Cells(1, "A"), .Cells(LastRow2, "A"))
End With

For Each cell In SalesMade

Set A = SalesmanList.Find(What:=cell.Value, LookIn:=xlValues)

If Not A Is Nothing Then
cell.Interior.ColorIndex = 3
End If

Next cell

Application.ScreenUpdating = True

End Sub

Hope this helps! If so, please give credit.
--
Cheers,
Ryan


"Robin" wrote:

> i have 2 sheets, sheet1 contains the sales made by many salesmen(one of the
> column is "names"), sheet2 contains a list of the names of the salesmen.
>
> i need to create a macro that will read all the names in sheet2 and compare
> it against the "name" column in sheet1, and then i want the macro to change
> the colour of all the of the whole row if a name from sheet2 matches a name
> in the "name" column in sheet1
>
> i am very new to coding macros so what every help u can provide me with will
> be greatly appriciated :-)

wow thx man, it really works very well, but could plz help me a little bit
more?
i need the whole row to be red, not just the cell where the name matches

Thanks and Regards
Robin

PS. how do i give credit(i.e. do i put ur name in the code as a comment?)

"RyanH" wrote:

> This should do the trick for you Robin. This code assumes that the list of
> sales people in each sheet are in Col. A. If not, you will have to change it
> or just let me know.
>
> Sub FindSalesman()
>
> Dim cell As Range
> Dim LastRow1 As Long
> Dim LastRow2 As Long
> Dim SalesmanList As Range
> Dim SalesMade As Range
> Dim A As Variant
>
> Application.ScreenUpdating = False
>
> With Sheets("Sheet1")
> LastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
> Set SalesMade = Range(.Cells(1, "A"), .Cells(LastRow1, "A"))
> End With
>
> With Sheets("Sheet2")
> LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
> Set SalesmanList = Range(.Cells(1, "A"), .Cells(LastRow2, "A"))
> End With
>
> For Each cell In SalesMade
>
> Set A = SalesmanList.Find(What:=cell.Value, LookIn:=xlValues)
>
> If Not A Is Nothing Then
> cell.Interior.ColorIndex = 3
> End If
>
> Next cell
>
> Application.ScreenUpdating = True
>
> End Sub
>
> Hope this helps! If so, please give credit.
> --
> Cheers,
> Ryan
>
>
> "Robin" wrote:
>
> > i have 2 sheets, sheet1 contains the sales made by many salesmen(one of the
> > column is "names"), sheet2 contains a list of the names of the salesmen.
> >
> > i need to create a macro that will read all the names in sheet2 and compare
> > it against the "name" column in sheet1, and then i want the macro to change
> > the colour of all the of the whole row if a name from sheet2 matches a name
> > in the "name" column in sheet1
> >
> > i am very new to coding macros so what every help u can provide me with will
> > be greatly appriciated :-)

change this line

cell.Interior.ColorIndex = 3

to

cell.entirerow.interior.colorindex = 3

unless you have a specific range you want to highlight

--


Gary


"Robin" <(E-Mail Removed)> wrote in message
news:FB5376ED-C430-477E-856A-(E-Mail Removed)...
> wow thx man, it really works very well, but could plz help me a little bit
> more?
> i need the whole row to be red, not just the cell where the name matches
>
> Thanks and Regards
> Robin
>
> PS. how do i give credit(i.e. do i put ur name in the code as a comment?)
>
> "RyanH" wrote:
>
>> This should do the trick for you Robin. This code assumes that the list of
>> sales people in each sheet are in Col. A. If not, you will have to change it
>> or just let me know.
>>
>> Sub FindSalesman()
>>
>> Dim cell As Range
>> Dim LastRow1 As Long
>> Dim LastRow2 As Long
>> Dim SalesmanList As Range
>> Dim SalesMade As Range
>> Dim A As Variant
>>
>> Application.ScreenUpdating = False
>>
>> With Sheets("Sheet1")
>> LastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
>> Set SalesMade = Range(.Cells(1, "A"), .Cells(LastRow1, "A"))
>> End With
>>
>> With Sheets("Sheet2")
>> LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
>> Set SalesmanList = Range(.Cells(1, "A"), .Cells(LastRow2, "A"))
>> End With
>>
>> For Each cell In SalesMade
>>
>> Set A = SalesmanList.Find(What:=cell.Value, LookIn:=xlValues)
>>
>> If Not A Is Nothing Then
>> cell.Interior.ColorIndex = 3
>> End If
>>
>> Next cell
>>
>> Application.ScreenUpdating = True
>>
>> End Sub
>>
>> Hope this helps! If so, please give credit.
>> --
>> Cheers,
>> Ryan
>>
>>
>> "Robin" wrote:
>>
>> > i have 2 sheets, sheet1 contains the sales made by many salesmen(one of the
>> > column is "names"), sheet2 contains a list of the names of the salesmen.
>> >
>> > i need to create a macro that will read all the names in sheet2 and compare
>> > it against the "name" column in sheet1, and then i want the macro to change
>> > the colour of all the of the whole row if a name from sheet2 matches a name
>> > in the "name" column in sheet1
>> >
>> > i am very new to coding macros so what every help u can provide me with
>> > will
>> > be greatly appriciated :-)

Just change this row:

> > cell.Interior.ColorIndex = 3

to this

cell.EntireRow.Interior.ColorIndex = 3

That should do it for you!

Hope I have helped. If so, please Click "Yes" the Post was helpful.
--
Cheers,
Ryan


"Robin" wrote:

> wow thx man, it really works very well, but could plz help me a little bit
> more?
> i need the whole row to be red, not just the cell where the name matches
>
> Thanks and Regards
> Robin
>
> PS. how do i give credit(i.e. do i put ur name in the code as a comment?)
>
> "RyanH" wrote:
>
> > This should do the trick for you Robin. This code assumes that the list of
> > sales people in each sheet are in Col. A. If not, you will have to change it
> > or just let me know.
> >
> > Sub FindSalesman()
> >
> > Dim cell As Range
> > Dim LastRow1 As Long
> > Dim LastRow2 As Long
> > Dim SalesmanList As Range
> > Dim SalesMade As Range
> > Dim A As Variant
> >
> > Application.ScreenUpdating = False
> >
> > With Sheets("Sheet1")
> > LastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
> > Set SalesMade = Range(.Cells(1, "A"), .Cells(LastRow1, "A"))
> > End With
> >
> > With Sheets("Sheet2")
> > LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
> > Set SalesmanList = Range(.Cells(1, "A"), .Cells(LastRow2, "A"))
> > End With
> >
> > For Each cell In SalesMade
> >
> > Set A = SalesmanList.Find(What:=cell.Value, LookIn:=xlValues)
> >
> > If Not A Is Nothing Then
> > cell.Interior.ColorIndex = 3
> > End If
> >
> > Next cell
> >
> > Application.ScreenUpdating = True
> >
> > End Sub
> >
> > Hope this helps! If so, please give credit.
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Robin" wrote:
> >
> > > i have 2 sheets, sheet1 contains the sales made by many salesmen(one of the
> > > column is "names"), sheet2 contains a list of the names of the salesmen.
> > >
> > > i need to create a macro that will read all the names in sheet2 and compare
> > > it against the "name" column in sheet1, and then i want the macro to change
> > > the colour of all the of the whole row if a name from sheet2 matches a name
> > > in the "name" column in sheet1
> > >
> > > i am very new to coding macros so what every help u can provide me with will
> > > be greatly appriciated :-)