Modifying code

R

Robin

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
 
N

Nigel

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
 
R

Robin

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top