Databases
StackOverflow2013
Postsdbo
POMatch values in same column across two sheets and then compare entire row for differences
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMatch values in same column across two sheets and then compare entire row for differences
    primarykey
    data
    text
    <p>I have the code form another post here, but I cannot seem to get it to actually highlight individual cell differences in each row. I have a current sheet and a previous sheet; the idea is that the code should compare serial numbers in one column (the same on both worksheets) and do two things:</p> <p>1) If a value appears on the Current sheet, but is not on the Previous, then the entire row on the Current sheet is highlighted Green. (This work with the current code); and 2) If a matching value is on both sheets then the rows should be compared and any value on the Current sheet that is different from the Previous is highlighted yellow. (This does not work)</p> <p>The number and order of columns is always the same. The serial numbers do not change and are unique to each entry. The code that I have been looking at is:</p> <pre><code>Sub NewUpdates() Const ID_COL As Integer = 31 'ID is in this column Const NUM_COLS As Integer = 32 'how many columns are being compared? Dim shtNew As Excel.Worksheet, shtOld As Excel.Worksheet Dim rwNew As Range, rwOld As Range, f As Range Dim x As Integer, Id Dim valOld, valNew Set shtNew = ActiveWorkbook.Sheets("CurrentList") Set shtOld = ActiveWorkbook.Sheets("PreviousList") Set rwNew = shtNew.Rows(5) 'first entry on "current" sheet Do While rwNew.Cells(ID_COL).Value &lt;&gt; "" Id = rwNew.Cells(ID_COL).Value Set f = shtOld.UsedRange.Columns(ID_COL).Find(Id, , xlValues, xlWhole) If Not f Is Nothing Then Set rwOld = f.EntireRow For x = 1 To NUM_COLS If rwNew.Cells(x).Value &lt;&gt; rwOld.Cells(x).Value Then rwNew.Cells.Interior.Color = vbYellow Else rwNew.Cells.Interior.ColorIndex = xlNone End If Next x Else rwNew.EntireRow.Interior.Color = vbGreen 'new entry End If Set rwNew = rwNew.Offset(1, 0) 'next row to compare Loop End Sub </code></pre> <p>I did not change much of anything in the coding itself, but the original discussion that I pulled this from did not continue any further. Any ideas on updating so that I can get it highlighting the individual cells to show differences?</p> <p>edit: Found the link where Tim Williams responded to a similar question and I found this code. It can be found <a href="https://stackoverflow.com/questions/8084097/excel-vba-compare-worksheet-rows-based-off-of-value">here</a>.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJon
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload