Cross Reference
 

Hello. On Sheet1 I have a block of data that resides in an area from D7
thru BA5000. Each row contains at least 1 populated cell, but almost all do
NOT go thru BA.

On Sheet2, I have a revised data set within the same range. Can I cross
reference the data in Sheet1 with Sheet2 and highlight each cell in Sheet1
that does not exist in Sheet2? I need to check each row independently (ie
check row 7 in sheet1 to see if something is not in row 7 of sheet2).

Basically, 2 reports are generated at 2 different points in time. I need to
make sure that the cells on a given row on sheet1 still exist somewhere on
row 7 in sheet2.

Thanks!

Cross Reference
 

Steph,

Put this code in Sheet1 (right click on Sheet1 tab, then select View Code)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim rw As Integer
rw = ActiveCell.Row
For i = 4 To 53
If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = "" Then
Cells(rw, i).Interior.ColorIndex = 6
Else
Cells(rw, i).Interior.ColorIndex = 0
End If
Next
End Sub

Each line you double click one Sheet1 will be compared to Sheet2

"Steph" wrote:

Hello. On Sheet1 I have a block of data that resides in an area from D7
thru BA5000. Each row contains at least 1 populated cell, but almost all do
NOT go thru BA.

On Sheet2, I have a revised data set within the same range. Can I cross
reference the data in Sheet1 with Sheet2 and highlight each cell in Sheet1
that does not exist in Sheet2? I need to check each row independently (ie
check row 7 in sheet1 to see if something is not in row 7 of sheet2).

Basically, 2 reports are generated at 2 different points in time. I need to
make sure that the cells on a given row on sheet1 still exist somewhere on
row 7 in sheet2.

Thanks!

Cross Reference
 

Hi. Thanks so much for the response. It works if an item is removed from
sheet2 and still exists in sheet1.

But, if I rename a data cell in sheet2, nothing happens.

For example, Sheet1 has 4 data cells of A,B,C, and D.
On the same row in Sheet2, if it has 3 data cells of A,C, and D, then B is
highlighted on Sheet1.

But, if in that same row on Sheet2, if I didn't delete the B but rather
renamed it to Z, nothing is highlighted.

So it looks like your code is checking if any data exists in the
corresponding cells rather than checking the contents of each cell.

Thanks again!!

"crazybass2" wrote in message
...
Steph,

Put this code in Sheet1 (right click on Sheet1 tab, then select View Code)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim rw As Integer
rw = ActiveCell.Row
For i = 4 To 53
If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = "" Then
Cells(rw, i).Interior.ColorIndex = 6
Else
Cells(rw, i).Interior.ColorIndex = 0
End If
Next
End Sub

Each line you double click one Sheet1 will be compared to Sheet2

"Steph" wrote:

Hello. On Sheet1 I have a block of data that resides in an area from D7
thru BA5000. Each row contains at least 1 populated cell, but almost
all do
NOT go thru BA.

On Sheet2, I have a revised data set within the same range. Can I cross
reference the data in Sheet1 with Sheet2 and highlight each cell in
Sheet1
that does not exist in Sheet2? I need to check each row independently
(ie
check row 7 in sheet1 to see if something is not in row 7 of sheet2).

Basically, 2 reports are generated at 2 different points in time. I
need to
make sure that the cells on a given row on sheet1 still exist somewhere
on
row 7 in sheet2.

Thanks!

Cross Reference
 

Hi. Also, it is quite possible that the order of the data cells changes
from sheet1 to sheet2, which is totally fine. I don't care about the order,
just that each data cell in a given row on sheet1 exists somewhere on the
same row on sheet2. Thanks again!!

"crazybass2" wrote in message
...
Steph,

Put this code in Sheet1 (right click on Sheet1 tab, then select View Code)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim rw As Integer
rw = ActiveCell.Row
For i = 4 To 53
If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = "" Then
Cells(rw, i).Interior.ColorIndex = 6
Else
Cells(rw, i).Interior.ColorIndex = 0
End If
Next
End Sub

Each line you double click one Sheet1 will be compared to Sheet2

"Steph" wrote:

Hello. On Sheet1 I have a block of data that resides in an area from D7
thru BA5000. Each row contains at least 1 populated cell, but almost
all do
NOT go thru BA.

On Sheet2, I have a revised data set within the same range. Can I cross
reference the data in Sheet1 with Sheet2 and highlight each cell in
Sheet1
that does not exist in Sheet2? I need to check each row independently
(ie
check row 7 in sheet1 to see if something is not in row 7 of sheet2).

Basically, 2 reports are generated at 2 different points in time. I
need to
make sure that the cells on a given row on sheet1 still exist somewhere
on
row 7 in sheet2.

Thanks!

Cross Reference
 

Steph,

Sorry about that...Your first post said if it didn't exist on Sheet2.
Here's the fix...

Replace this line

If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = "" Then


With this line

If Not Cells(rw, i).Value = Sheet2.Cells(rw, i).Value Then


That should give the desired effect.

Mike



"Steph" wrote:

Hi. Thanks so much for the response. It works if an item is removed from
sheet2 and still exists in sheet1.

But, if I rename a data cell in sheet2, nothing happens.

For example, Sheet1 has 4 data cells of A,B,C, and D.
On the same row in Sheet2, if it has 3 data cells of A,C, and D, then B is
highlighted on Sheet1.

But, if in that same row on Sheet2, if I didn't delete the B but rather
renamed it to Z, nothing is highlighted.

So it looks like your code is checking if any data exists in the
corresponding cells rather than checking the contents of each cell.

Thanks again!!

"crazybass2" wrote in message
...
Steph,

Put this code in Sheet1 (right click on Sheet1 tab, then select View Code)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim rw As Integer
rw = ActiveCell.Row
For i = 4 To 53
If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = "" Then
Cells(rw, i).Interior.ColorIndex = 6
Else
Cells(rw, i).Interior.ColorIndex = 0
End If
Next
End Sub

Each line you double click one Sheet1 will be compared to Sheet2

"Steph" wrote:

Hello. On Sheet1 I have a block of data that resides in an area from D7
thru BA5000. Each row contains at least 1 populated cell, but almost
all do
NOT go thru BA.

On Sheet2, I have a revised data set within the same range. Can I cross
reference the data in Sheet1 with Sheet2 and highlight each cell in
Sheet1
that does not exist in Sheet2? I need to check each row independently
(ie
check row 7 in sheet1 to see if something is not in row 7 of sheet2).

Basically, 2 reports are generated at 2 different points in time. I
need to
make sure that the cells on a given row on sheet1 still exist somewhere
on
row 7 in sheet2.

Thanks!

Cross Reference
 

Hi Mike. I forgot to include one more thing in my last post, so I quick
sent a second post - but I don't think you got it. Anyway, here it is:

Hi. Also, it is quite possible that the order of the data cells changes
from sheet1 to sheet2, which is totally fine. I don't care about the order,
just that each data cell in a given row on sheet1 exists somewhere on the
same row on sheet2. Thanks again!!

So your revised code fixes the first problem, but unfortunately not the
order change problem. Thanks again Mike for all your help! Much
appreciated.


"crazybass2" wrote in message
...
Steph,

Sorry about that...Your first post said if it didn't exist on Sheet2.
Here's the fix...

Replace this line

If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = "" Then


With this line

If Not Cells(rw, i).Value = Sheet2.Cells(rw, i).Value Then


That should give the desired effect.

Mike



"Steph" wrote:

Hi. Thanks so much for the response. It works if an item is removed
from
sheet2 and still exists in sheet1.

But, if I rename a data cell in sheet2, nothing happens.

For example, Sheet1 has 4 data cells of A,B,C, and D.
On the same row in Sheet2, if it has 3 data cells of A,C, and D, then B
is
highlighted on Sheet1.

But, if in that same row on Sheet2, if I didn't delete the B but rather
renamed it to Z, nothing is highlighted.

So it looks like your code is checking if any data exists in the
corresponding cells rather than checking the contents of each cell.

Thanks again!!

"crazybass2" wrote in message
...
Steph,

Put this code in Sheet1 (right click on Sheet1 tab, then select View
Code)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
Dim rw As Integer
rw = ActiveCell.Row
For i = 4 To 53
If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = ""
Then
Cells(rw, i).Interior.ColorIndex = 6
Else
Cells(rw, i).Interior.ColorIndex = 0
End If
Next
End Sub

Each line you double click one Sheet1 will be compared to Sheet2

"Steph" wrote:

Hello. On Sheet1 I have a block of data that resides in an area
from D7
thru BA5000. Each row contains at least 1 populated cell, but
almost
all do
NOT go thru BA.

On Sheet2, I have a revised data set within the same range. Can I
cross
reference the data in Sheet1 with Sheet2 and highlight each cell in
Sheet1
that does not exist in Sheet2? I need to check each row
independently
(ie
check row 7 in sheet1 to see if something is not in row 7 of
sheet2).

Basically, 2 reports are generated at 2 different points in time. I
need to
make sure that the cells on a given row on sheet1 still exist
somewhere
on
row 7 in sheet2.

Thanks!

Cross Reference
 

Got it. Thanks again for your help Mike:

Dim rw As Integer
c = 0
rw = ActiveCell.Row
For i = 4 To 53
For j = 4 To 53
If Sheet1.Cells(rw, i).Value = Sheet2.Cells(rw, j).Value Then
c = c + 1
End If
Next
If c 0 Then
Cells(rw, i).Interior.ColorIndex = 0
Else
Cells(rw, i).Interior.ColorIndex = 35
End If
c = 0
Next


"crazybass2" wrote in message
...
Steph,

Sorry about that...Your first post said if it didn't exist on Sheet2.
Here's the fix...

Replace this line

If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = "" Then


With this line

If Not Cells(rw, i).Value = Sheet2.Cells(rw, i).Value Then


That should give the desired effect.

Mike



"Steph" wrote:

Hi. Thanks so much for the response. It works if an item is removed
from
sheet2 and still exists in sheet1.

But, if I rename a data cell in sheet2, nothing happens.

For example, Sheet1 has 4 data cells of A,B,C, and D.
On the same row in Sheet2, if it has 3 data cells of A,C, and D, then B
is
highlighted on Sheet1.

But, if in that same row on Sheet2, if I didn't delete the B but rather
renamed it to Z, nothing is highlighted.

So it looks like your code is checking if any data exists in the
corresponding cells rather than checking the contents of each cell.

Thanks again!!

"crazybass2" wrote in message
...
Steph,

Put this code in Sheet1 (right click on Sheet1 tab, then select View
Code)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
Dim rw As Integer
rw = ActiveCell.Row
For i = 4 To 53
If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = ""
Then
Cells(rw, i).Interior.ColorIndex = 6
Else
Cells(rw, i).Interior.ColorIndex = 0
End If
Next
End Sub

Each line you double click one Sheet1 will be compared to Sheet2

"Steph" wrote:

Hello. On Sheet1 I have a block of data that resides in an area
from D7
thru BA5000. Each row contains at least 1 populated cell, but
almost
all do
NOT go thru BA.

On Sheet2, I have a revised data set within the same range. Can I
cross
reference the data in Sheet1 with Sheet2 and highlight each cell in
Sheet1
that does not exist in Sheet2? I need to check each row
independently
(ie
check row 7 in sheet1 to see if something is not in row 7 of
sheet2).

Basically, 2 reports are generated at 2 different points in time. I
need to
make sure that the cells on a given row on sheet1 still exist
somewhere
on
row 7 in sheet2.

Thanks!

Cross Reference
 

Good fix! You've got the idea now.


Mike

"Steph" wrote:

Got it. Thanks again for your help Mike:

Dim rw As Integer
c = 0
rw = ActiveCell.Row
For i = 4 To 53
For j = 4 To 53
If Sheet1.Cells(rw, i).Value = Sheet2.Cells(rw, j).Value Then
c = c + 1
End If
Next
If c 0 Then
Cells(rw, i).Interior.ColorIndex = 0
Else
Cells(rw, i).Interior.ColorIndex = 35
End If
c = 0
Next


"crazybass2" wrote in message
...
Steph,

Sorry about that...Your first post said if it didn't exist on Sheet2.
Here's the fix...

Replace this line

If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = "" Then


With this line

If Not Cells(rw, i).Value = Sheet2.Cells(rw, i).Value Then


That should give the desired effect.

Mike



"Steph" wrote:

Hi. Thanks so much for the response. It works if an item is removed
from
sheet2 and still exists in sheet1.

But, if I rename a data cell in sheet2, nothing happens.

For example, Sheet1 has 4 data cells of A,B,C, and D.
On the same row in Sheet2, if it has 3 data cells of A,C, and D, then B
is
highlighted on Sheet1.

But, if in that same row on Sheet2, if I didn't delete the B but rather
renamed it to Z, nothing is highlighted.

So it looks like your code is checking if any data exists in the
corresponding cells rather than checking the contents of each cell.

Thanks again!!

"crazybass2" wrote in message
...
Steph,

Put this code in Sheet1 (right click on Sheet1 tab, then select View
Code)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
Dim rw As Integer
rw = ActiveCell.Row
For i = 4 To 53
If Not Cells(rw, i).Value = "" And Sheet2.Cells(rw, i).Value = ""
Then
Cells(rw, i).Interior.ColorIndex = 6
Else
Cells(rw, i).Interior.ColorIndex = 0
End If
Next
End Sub

Each line you double click one Sheet1 will be compared to Sheet2

"Steph" wrote:

Hello. On Sheet1 I have a block of data that resides in an area
from D7
thru BA5000. Each row contains at least 1 populated cell, but
almost
all do
NOT go thru BA.

On Sheet2, I have a revised data set within the same range. Can I
cross
reference the data in Sheet1 with Sheet2 and highlight each cell in
Sheet1
that does not exist in Sheet2? I need to check each row
independently
(ie
check row 7 in sheet1 to see if something is not in row 7 of
sheet2).

Basically, 2 reports are generated at 2 different points in time. I
need to
make sure that the cells on a given row on sheet1 still exist
somewhere
on
row 7 in sheet2.

Thanks!