Compare Sheets values by .find loop?

[Office_Novice]

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.

 

[Ivyleaf]

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.

 

[Office_Novice]

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]

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









- Show quoted text -

Hi,

I'm puzzled that the code I posted didn't work for you. When you say
it didn't work, do you mean you were getting an error, or that it
wasn't finding anything or something else?

I would say if it didn't find what you expected, that you haven't
redefined the ranges quickly.

Using my original code, you would have to change the following lines:

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

to:

Set SrcList = Sheets(???).Range("???:???")
Set ChkList = Sheets(1).Range("C2:C6000")

Just replace the question marks with the correct range... you haven't
said where the list of values you want to check for is located.

Cheers,
Ivan.

 

[Office_Novice]

you're earlier post only returnedthe Cell adress in the msgbox. I needed
somthing more like this

Sub compareVals()

Dim Cell As Range
With Worksheets(1).Range("C2:C60000")

Do
On Error Resume Next
Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues)

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 Until ActiveCell = ""
End With
End Sub

 

[Ivyleaf]

you're earlier post only returnedthe Cell adress in the msgbox.  I needed
somthing more like this

Sub compareVals()

  Dim Cell As Range
  With Worksheets(1).Range("C2:C60000")

  Do
  On Error Resume Next
  Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues)

    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 Until ActiveCell = ""
  End With
End Sub










- Show quoted text -

Hi,

Sorry, I obviously didn't clarify my initial code properly. It was
only designed to be a proof of concept as I thought from the sound of
your post the main trouble you were having was with looping through
the list and finding the values. As such, I thought you could just
replace the appropriate msgbox line with whatever you wanted to happen
- i.e. change colour of the cell.
The following code should (maybe) be more suitable:

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


Set SrcList = Intersect(Sheets(2).Columns(1),Sheets(2).UsedRange)
Set ChkList = Intersect(Sheets(1).Columns(3),Sheets(1).UsedRange)

On Error Resume Next
For Each cell In SrcList
Set FoundRng = ChkList.Find(cell, LookAt:=xlWhole)
If FoundRng Is Nothing Then
cell.Interior.ColorIndex = 6
End If
Next

For Each cell In ChkList
Set FoundRng = SrcList.Find(cell, LookAt:=xlWhole)
If FoundRng Is Nothing Then
cell.Interior.ColorIndex = 6
End If
Next
End Sub

 

[kounoike]

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