Compare Sheets values by .find loop?
 

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.

Compare Sheets values by .find loop?
 

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.

Compare Sheets values by .find loop?
 

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.

Compare Sheets values by .find loop?
 

On Apr 7, 8:25*am, Office_Novice
wrote:
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.- Hide quoted text -

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

Compare Sheets values by .find loop?
 

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" wrote:

On Apr 7, 8:25 am, Office_Novice
wrote:
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.- Hide quoted text -

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

Compare Sheets values by .find loop?
 

On Apr 7, 10:22*am, Office_Novice
wrote:
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" wrote:
On Apr 7, 8:25 am, Office_Novice
wrote:
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.- Hide quoted text -

- 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.- Hide quoted text -

- 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

Compare Sheets values by .find loop?
 

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.