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