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

[Matthew Dyer]

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

 

[OssieMac]

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

 

[Matthew Dyer]

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

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


how do referance a different workbook using the above code?

 

[OssieMac]

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