PDA

View Full Version : Solved: Comparing a list between two worksheets



coopeed
04-04-2007, 09:16 AM
Hi. I just discovered this forum and it looks quite helpful. Anyways, I've tried to modify some old code that compares the list of names on one sheet in column A to another sheet in column K. Any name that appears on both will be highlighted yellow on the first sheet, otherwise it will be highlighted green.
The problem I am experiencing it that only the first name is highlighted and the other names are not looked at.
I'd appreciate any assistance with this code. Thanks.

Sub Test()
Dim cell As Range
Dim rngA As Range
Dim rngB As Range

Set rngA = Sheets("ApprovalAmounts").Range("A2:A" & Sheets("ApprovalAmounts").Range("A65536").End(xlUp).Row)
Set rngB = Sheets("CostCenters").Range("K2:K" & Sheets("CostCenters").Range("K65536").End(xlUp).Row)
Sheets("ApprovalAmounts").Select
For Each cell In rngA
If Application.CountIf(rngB, cell.Value) = 0 Then
Selection.Interior.ColorIndex = 36
Else
Selection.Interior.ColorIndex = 15
End If
Next
End Sub

austenr
04-04-2007, 09:53 AM
Hi welcome to VBAX!!! It would be most helpful if you could post a sample of your data. Sanatized as necessary to get the best help.

coopeed
04-04-2007, 10:10 AM
Ok. Basically, this is what I have.

ApprovalAmount worksheet
Column A Column B
John 100
Bill 500
Tom 200

CostCenters worksheet
Column K Column L
Tom 200
John 100

Again, if the name in column A on the ApprovalAmount worksheet is on the CostCenter worksheet in column K, then highlight the name in yellow only on the ApprovalAmount worksheet. If the name does not appear, highlight green. In this example, John and Tom will be highlighted yellow and Bill will be highlighted green.

Also, there are approximately 400 names on the ApprovalAmount worksheet and 4000 names on the CostCenters worksheet.

mvidas
04-04-2007, 10:12 AM
Hi coopeed,

You don't actually need the line 'Sheets("ApprovalAmounts").Select'

Inside your loop, replace "Selection" with your "Cell" variable to have it change the color of the cell being examined by the code.

Matt

Charlize
04-04-2007, 10:49 AM
Sub Test()
Dim cella As Range
Dim vfound As Range
Dim rngA As Range
Dim rngB As Range
Set rngA = Sheets(1).Range("A2:A" & Sheets(1).Range("A65536").End(xlUp).Row)
Set rngB = Sheets(2).Range("K2:K" & Sheets(2).Range("K65536").End(xlUp).Row)
Sheets(1).Select
For Each cella In rngA
With rngB
Set vfound = .Find(cella.Value, LookIn:=xlValues)
If Not vfound Is Nothing Then
cella.Interior.ColorIndex = 6
Else
cella.Interior.ColorIndex = 15
End If
End With
Next cella
End Sub
Charlize

coopeed
04-04-2007, 11:39 AM
Thanks for your responses Matt and Charlize.

mdmackillop
04-04-2007, 12:56 PM
Hi coopeed,
If this is solved, you can mark it so using the Thread Tools dropdowm.