Macro to Compare 2 Tables and highlight 3 defined differences

Hi, I am am in need of a macro to compare 2 tables - both in the same workbook, on 2 different sheets.

Table1 is a refreshable table with data updated daily
Table2 is a table with only specific rows from table1 that were added to a new sheet previously (Sheet Name = Workload Tracking)

table 1 starts at row B21 (headers)

So, when I run the macro, I'd like to be able to highlight anything that has changed in table1 compared to the data that is visible in the columns of in table2. Table 2 will have 1 column (1) that will be in table 2 --- and table 2 will have the first 4 columns that will not be in table1 and should be ignore -- so, column 2 of table1 = column 5 of table2... and all columns after that will be the same.

3 different forms of highlighting for the differences
1. Red = If the "FileNo" is no longer on table1 but is on table 2 (Highlight entire table row red)
2. Green = If the data in table1 is filled in, but the data in table2 is not (data = the specific cell, found by the "FileNumber" row, and the column header name)
3. Yellow = If the data in table1 is filled in, and the data in table2 is filled in, but there is a difference

The comparison needs to be by "FileNo" (This is a unique value) and the Column Headers (visible in table2 but all of table 1 are possibilities, depending on which columns the user has hidden in table2) name and the data that is contained in the other, visible, columns

I have attached a workbook with notes, explaining in more detail as I think that will be clearer

Table1 actual name in my data = Table_CollPreComplaint and is on worksheet "Dashboard"
Table2 actual name in my data = Table_WorkloadTracking as is on worksheet "Workload Tracking"

--- In the example attached - both are named the same as my actual workbook dashboard

This example is MUCH smaller than the actual workbook, which is around 15,000 rows --- and shows both tables, with example columns hidden -- i hope it clarifies everything but if not, please let me know and ill be happy to clarify!

Appreciate any help!

Awesome, I will give it a go this evening! Thank you for taking a look!

This works great -- thank you! The one tweak I have, and this is my fault for not specifying, is that I would also need to transfer over the data contained within the cell in the Dashboard Table for fields that are different (so, yellow and green) -- that way, I can see not only what has changed but also the new value

Yessir - Perfect. Thank you!

Question - What range is this referring to? For jR = 9 To 47

at fist i assumed columns but that would be strange since it starts at 9..

also, have been getting a type mismatch error at this line when running.. If shD.Cells(rng.Row, jD) <> "" And shW.Cells(cl.Row, jR) = "" Then

The only thing I changed is adding an error catch, application false/tru's... and "A" to "B" in this line.. and AU to BC..

With shW.Range(shW.Cells(cl.Row, "B"), shW.Cells(cl.Row, "BC")).Interior

Please Login or Register  to view this content.

ahh, that makes sense --- 10-4 will do tomorrow morning -- thanks!

Okay, so, using the attached macro (Updated jr to 6 to 71) I am assuming that the column numbers here are the sheet columns and not the table columns..

Using the original Code line - [If shD.Cells(rng.Row, jD) <> "" And shW.Cells(cl.Row, jR) = "" Then] - the variable values at the time of error are:
rng.Row = 3660
jD = 6
cl.row = 173
jR = 9

Using [If Len(shD.Cells(rng.Row, jD)) <> 0 And Len(shW.Cells(cl.Row, jR)) = 0 Then] I get the same error.. with same variable values at time of error
rng.Row = 3660
jD = 6
cl.Row = 173
jR = 9
Len(shW.Cells(cl.Row, jR)) = "<"type Mismatch">" --- had to put quotes on this to avoid html block


rng.Row 3660 and cl.Row 173 are the same FileNo

Okay, I see what's happening -- If the value on either table = N/A (I have some macros that run and assign a 25 digit ID code to the account to identify what stage its in.. often times, since its 25 digits, the ID hasn't been translated yet since this is a new dashboard. So, I guess what needs to happen is that the code needs to be able to recognize the N/A as a value and then mark it as a non-match

I went ahead and added the code to copy the entire table onto itself as values at the end of the macro's and re-ran but then it gave the same error. I copy each cell into another sheet and did the == and it was true. One thing that was different between them is the font and size of font... not sure if that would have an effect though?