Comparing data in multiple columns to another sheet
Closed
savagea
Posts
1
Registration date
Tuesday April 26, 2016
Status
Member
Last seen
April 27, 2016
-
Apr 27, 2016 at 08:29 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 3, 2016 at 12:03 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 3, 2016 at 12:03 PM
Related:
- Comparing data in multiple columns to another sheet
- Transfer data from one excel worksheet to another automatically - Guide
- Little alchemy cheat sheet - Guide
- Display two columns in data validation list but return only one - Guide
- Google sheet right to left - Guide
- How to allow multiple downloads in chrome - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
May 3, 2016 at 12:03 PM
May 3, 2016 at 12:03 PM
Hi Angela,
In the code below the sheet of names and addresses is called Sheet1 and the new report sheet is called Sheet2.
The code will loop through column B of Sheet2. When the address is not found (new address) or the corresponding name doesn't match (new occupant) on Sheet1, the name and address on Sheet2 will turn orange.
Hopefully it is how you imagined it.
Here is the code:
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
In the code below the sheet of names and addresses is called Sheet1 and the new report sheet is called Sheet2.
The code will loop through column B of Sheet2. When the address is not found (new address) or the corresponding name doesn't match (new occupant) on Sheet1, the name and address on Sheet2 will turn orange.
Hopefully it is how you imagined it.
Here is the code:
Sub RunMe()
Dim x As Integer, vFound As Range
Sheets("Sheet2").Select
For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
Set vFound = Sheets("Sheet1").Columns("B:B").Find(what:=cell)
If vFound Is Nothing Then
Range(Range("A" & cell.Row), Range("B" & cell.Row)).Interior.Color = 49407
ElseIf cell & cell.Offset(0, -1) <> vFound & vFound.Offset(0, -1) Then
Range(Range("A" & cell.Row), Range("B" & cell.Row)).Interior.Color = 49407
End If
Next cell
End Sub
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
