Changing colour bases on another sheet
 

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 :-)

Changing colour bases on another sheet
 

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 :-)

Changing colour bases on another sheet
 

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 :-)

Changing colour bases on another sheet
 

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" wrote in message
...
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 :-)

Changing colour bases on another sheet
 

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 :-)