compare value of cell in one sheet to range of another sheet
 

Still learning how to use VB coding to it's most effectiveness...

I have two sheets. sheet1 and sheet2. I want to pull the value of
cell(2, "d") from sheet1 and compare it to every value of column "d"
in sheet2, highlighting as it goes by. Once it has finished I want it
to go to cell(3, "d") in sheet1 and do the same thing, comparing
itself to all the values of column "d" in sheet2. I know how to pull
the variable I want, I know how to do the highlighting code, and I
know how to do the loops and whatnot. What I do not know how to do is
referance seperate sheets. help??

compare value of cell in one sheet to range of another sheet
 

Hello Matthew,

I found it was going to be a bit difficult to describe the use of assigned
ranges without actually writing example code required to do what you were
referring to.

Basically when a range is assigned to a range variable using the Set
statement, the range can be referred to from anywhere without further
reference to the worksheet to which it belongs because the worksheet info is
embedded in the range variable.

If you are using a different method of looping other than the For Each and
you want to use the range variable in your code then I will try to
incorporate it if you post the code you have.

Also I was not sure what you meant by Highlight so I just set the interior
color to yellow.

Sub Example()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngDsht1 As Range
Dim rngDsht2 As Range
Dim c As Range
Dim cToFind As Range
Dim firstAddr As String

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

With ws1
Set rngDsht1 = .Range(.Cells(2, "D"), _
.Cells(.Rows.Count, "D").End(xlUp))
End With

With ws2
Set rngDsht2 = .Range(.Cells(2, "D"), _
.Cells(.Rows.Count, "D").End(xlUp))
End With

For Each c In rngDsht1

With rngDsht2
Set cToFind = .Find(What:=c.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not cToFind Is Nothing Then
firstAddr = cToFind.Address
Do
cToFind.Interior.ColorIndex = 6
Set cToFind = .FindNext(cToFind)
Loop While cToFind.Address < firstAddr
End If
End With

Next c

End Sub


--
Regards,

OssieMac

compare value of cell in one sheet to range of another sheet
 

On Mar 8, 9:32*pm, OssieMac
wrote:
HelloMatthew,

I found it was going to be a bit difficult to describe the use of assigned
ranges without actually writing example code required to do what you were
referring to.

Basically when a range is assigned to a range variable using the Set
statement, the range can be referred to from anywhere without further
reference to the worksheet to which it belongs because the worksheet info is
embedded in the range variable.

If you are using a different method of looping other than the For Each and
you want to use the range variable in your code then I will try to
incorporate it if you post the code you have.

Also I was not sure what you meant by Highlight so I just set the interior
color to yellow.

Sub Example()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngDsht1 As Range
Dim rngDsht2 As Range
Dim c As Range
Dim cToFind As Range
Dim firstAddr As String

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

With ws1
* Set rngDsht1 = .Range(.Cells(2, "D"), _
* * .Cells(.Rows.Count, "D").End(xlUp))
End With

With ws2
* Set rngDsht2 = .Range(.Cells(2, "D"), _
* * .Cells(.Rows.Count, "D").End(xlUp))
End With

For Each c In rngDsht1

* With rngDsht2
* * Set cToFind = .Find(What:=c.Value, _
* * * LookIn:=xlFormulas, _
* * * LookAt:=xlWhole, _
* * * SearchOrder:=xlByRows, _
* * * SearchDirection:=xlNext, _
* * * MatchCase:=False, _
* * * SearchFormat:=False)

* * If Not cToFind Is Nothing Then
* * * firstAddr = cToFind.Address
* * * Do
* * * * cToFind.Interior.ColorIndex = 6
* * * * Set cToFind = .FindNext(cToFind)
* * * Loop While cToFind.Address < firstAddr
* * End If
* End With

Next c

End Sub

--
Regards,

OssieMac

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")


how do referance a different workbook using the above code?

compare value of cell in one sheet to range of another sheet
 

Hi Matthew,

Try the following. Both workbooks need to be open with the code I have
provided.

Sub Example()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngDsht1 As Range
Dim rngDsht2 As Range
Dim c As Range
Dim cToFind As Range
Dim firstAddr As String

Set wb1 = Workbooks("MyBook1.xls")
Set wb2 = Workbooks("MyBook2.xls")

Set ws1 = wb1.Sheets("Sheet1")
Set ws2 = wb2.Sheets("Sheet2")

With ws1
Set rngDsht1 = .Range(.Cells(2, "D"), _
.Cells(.Rows.Count, "D").End(xlUp))
End With

With ws2
Set rngDsht2 = .Range(.Cells(2, "D"), _
.Cells(.Rows.Count, "D").End(xlUp))
End With

For Each c In rngDsht1

With rngDsht2
Set cToFind = .Find(What:=c.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not cToFind Is Nothing Then
firstAddr = cToFind.Address
Do
cToFind.Interior.ColorIndex = 6
Set cToFind = .FindNext(cToFind)
Loop While cToFind.Address < firstAddr
End If
End With

Next c

End Sub


--
Regards,

OssieMac