Excel Mini Challenge
Closed
JeffAce
TrowaD
- Posts
- 2
- Registration date
- Tuesday November 13, 2012
- Status
- Member
- Last seen
- November 14, 2012
TrowaD
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
Related:
- Excel Mini Challenge
- Opera mini - Download
- Mac mini m1x release date - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to change date format in excel - Guide
- Excel @ in formula ✓ - Forum - Excel
2 replies
TrowaD
Nov 13, 2012 at 10:16 AM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
Nov 13, 2012 at 10:16 AM
Hi Jeff,
A cell with a formula cannot change the contents of other cells.
This means we need to use a macro:
Does this suit your needs?
Best regards,
Trowa
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
Does this suit your needs?
Best regards,
Trowa
JeffAce
Nov 14, 2012 at 04:30 AM
- Posts
- 2
- Registration date
- Tuesday November 13, 2012
- Status
- Member
- Last seen
- November 14, 2012
Nov 14, 2012 at 04:30 AM
Hi Trowa,
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.
Many thanks again
Jeff
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.
Many thanks again
Jeff
TrowaD
Nov 19, 2012 at 09:59 AM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
Nov 19, 2012 at 09:59 AM
Hi Jeff,
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.
Best regards,
Trowa
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.
Best regards,
Trowa