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

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

On Mar 8, 9:32*pm, OssieMac <Ossie...@discussions.microsoft.com>
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 infois
> 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?

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