I need to compare 2 versions of the same data which reside on 2 different worksheets (within the same workbook).
On the first sheet (the master sheet), Column B is used to capture 'PurchaseDate' and on the second sheet Column D is used to capture the same data (ie 'PurchaseDate'). Each row on both sheets has a unique identifier (called 'ID') which is captured in Column A of each sheet, which can be used to create a Vlookup for comparison.
What I need is to a formula that will do all of the following:
Compare the Purchase Date on both sheets for each record
If the Purchase Date (Column B)is different on the first Sheet from the 2nd sheet
Then:
update Purchase Date (Column D) on the 2nd sheet with the value on the 1st sheet
+ colour Purchase Date (Column D) on the 2nd sheet in dark blue with white font. If the value on the first sheet is null or the record doesnt exist, then retain the value on the second sheet or replace nulls with ""
Else (if both values are the same)
Then Do nothing (ie retain the value on the 2nd sheet
I created a VLookup to find the value on the first sheet without any problem but I am stuck in a circular reference on the bit where I want it to update the value if different on sheet1 or do nothing if the value is the same on both sheets. Any help would be greatly appreciated.
A cell with a formula cannot change the contents of other cells.
This means we need to use a macro:
Sub test()
Dim lRow, cRow As Integer
Sheets("Sheet2").Select
lRow = Range("D" & Rows.Count).End(xlUp).Row
For Each cell In Range("D2:D" & lRow)
cRow = cell.Row
If cell.Value = Sheets("Sheet1").Cells(cRow, "B").Value Then GoTo Nextcell
If Sheets("Sheet1").Cells(cRow, "B").Value = vbNullString Then GoTo Nextcell
If cell.Value <> Sheets("Sheet1").Cells(cRow, "B").Value Then
cell.Value = Sheets("Sheet1").Cells(cRow, "B").Value
cell.Interior.ColorIndex = 11
cell.Font.ColorIndex = 2
End If
Nextcell:
Next cell
End Sub
I hadnt thought of using macros as I thought it could be done through a formula at the cell level directly.
The macro does look like it will do what I need it to do except I dont understand how it picks up the right records on both sheets for comparison first. What I mean is let's say on sheet 1 we have records with the unique IDs 1, 2, 3, 4, 5 & 10, and on sheet 2 we have records with unique IDs 1, 2, 5, 6, 7, 8, 9 & 10 ... how will the macro pick the right records when comparing the values in Column B on sheet 1 and Column D on sheet 2? I cannot see where it is specified in the procedure for the macro to use the ID column to compare the values on the rows in each sheet.
Hope my explanation above makes sense ... if not I will still give it a try when I can and let you know the outcome ... this is a pet project currently.
You are right, the ID's are not in the code, since I didn't thought they were necessary.
Guess I was wrong.
So first we need to find matches of ID's and then check it's date.
Example:
Sheet1 A6 = 5
Sheet2 A4 = 5
Then compare Sheet1 B6 with Sheet2 D4.
Please comfirm that this is right (or not).
ID's are unique right?, no more then 1 match can be found.
Good to see it's just a pet project since my time is pretty limited the next two weeks.
