Another approach. try this one.

If your range is different from below, then change
With Worksheets("Sheet1")
Set src = Range(.Cells(1, "A"), .Cells(1, "A").End(xlDown))
End With
and
Set dst = Worksheets("Sheet2").Range("C2:C6000")
to suitable for your case.

Sub Comparetest()
Dim Cell As Range, src As Range, dst As Range
Dim k

With Worksheets("Sheet1")
Set src = Range(.Cells(1, "A"), .Cells(1, "A").End(xlDown))
End With

Set dst = Worksheets("Sheet2").Range("C2:C6000")

With Application
For Each Cell In src
k = .Match(Cell.Value, dst, 0)
If Not IsError(k) Then
.Index(dst, k, 1).Interior.ColorIndex = 6
Else
Cell.Interior.ColorIndex = 3
End If
Next
End With
End Sub

keiji

"Office_Novice" wrote in message
...
That didnt work at all. Thanks for trying though.

I have written this & it does what i want but only if i manualy change the
active cell
stop and restart the macro Could use some help modifying the code

Option Explicit

Sub compareRng()

Dim Cell As Range
With Worksheets(1).Range("C2:C6000")
Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues)

Do
If Not Cell Is Nothing Then
Cell.Interior.ColorIndex = 6
ElseIf Cell Is Nothing Then
ActiveCell.Interior.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Loop
End With
End Sub






"Ivyleaf" wrote:

On Apr 6, 7:41 am, Office_Novice
wrote:
greetings

i am stuck, i have two lists, both with the same information, but in
differnt places what i need is this

If Cell A1. value = "Anything on sheet (2) Column A" then do nothing

else if Cell A1. Value < "Anything on sheet (2) Column A" then

highlight A1, I know not too tough, but here is where i get stuck..

After searching for Cell A1.value i need to search the rest of column
A's
cells against Sheet (2) Column A

any help would be great.

Hi,

Sub compareVals()
Dim SrcList As Range, ChkList As Range
Dim cell As Range, FoundRng As Range

Set SrcList = Range("C1:C19")
Set ChkList = Range("A1:A19")

On Error Resume Next
For Each cell In SrcList
Set FoundRng = ChkList.Find(cell)
If FoundRng Is Nothing Then
MsgBox "No match for " & cell & " in " & ChkList.Address
Else
MsgBox "Found " & cell & " in " & ChkList.Address
End If
Next
End Sub

This will loop through one range and let you know if it finds the cell
from range 1 in range 2 or not. Just change the definition of the
ranges to match you needs.

Cheers,
Ivan.