Modifying code
 

can any1 plz help me change this code so that when it is executed, it will
colour rows in the ActiveSheet, not the "Summary" sheet.

Regards
Robin


Sub Highlighter()

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("Summary")
LastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
Set SalesMade = Range(.Cells(1, "C"), .Cells(LastRow1, "C"))
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.EntireRow.Interior.ColorIndex = 3
End If

Next cell

Application.ScreenUpdating = True

End Sub

Modifying code
 

Your code refers to two sheets "Summary" and "Sheet2"; these are interacted
in the code. It might be just a case of change the line

With Sheets("Summary")

to

With ActiveSheet


--

Regards,
Nigel




"Robin" wrote in message
...
can any1 plz help me change this code so that when it is executed, it will
colour rows in the ActiveSheet, not the "Summary" sheet.

Regards
Robin


Sub Highlighter()

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("Summary")
LastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
Set SalesMade = Range(.Cells(1, "C"), .Cells(LastRow1, "C"))
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.EntireRow.Interior.ColorIndex = 3
End If

Next cell

Application.ScreenUpdating = True

End Sub

Modifying code
 

ah ic so thats how its done.

what i did was this : With Sheets(ActiveSheet)

well thanks a lot, the code works perfectly now! :-)

Thanks and Regards
Robin

"Nigel" wrote:

Your code refers to two sheets "Summary" and "Sheet2"; these are interacted
in the code. It might be just a case of change the line

With Sheets("Summary")

to

With ActiveSheet


--

Regards,
Nigel




"Robin" wrote in message
...
can any1 plz help me change this code so that when it is executed, it will
colour rows in the ActiveSheet, not the "Summary" sheet.

Regards
Robin


Sub Highlighter()

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("Summary")
LastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
Set SalesMade = Range(.Cells(1, "C"), .Cells(LastRow1, "C"))
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.EntireRow.Interior.ColorIndex = 3
End If

Next cell

Application.ScreenUpdating = True

End Sub