Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cross reference | New Users to Excel | |||
Cross reference | Excel Discussion (Misc queries) | |||
cross reference two | New Users to Excel | |||
Cross Reference | Excel Worksheet Functions | |||
Cross reference? | Excel Programming |