Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to protect sheet allowing grouping and ungrouping but alsoallowing changing colour of specific cells | Excel Discussion (Misc queries) | |||
Changing background colour when changing data in a cell | Excel Discussion (Misc queries) | |||
Changing all cells in one colour to a different colour | Excel Discussion (Misc queries) | |||
Changing a cell colour | Excel Discussion (Misc queries) | |||
Changing Sheet Colour/Color | Excel Discussion (Misc queries) |