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