Vba to highlight the cell and remove duplicates

A

ashupuneet

New Member
Joined
Nov 8, 2016
Messages
9
HI
I need a help in preparing a macro which identify the duplicate in column C of sheet1 followed by removing old row and retain new row but before deletion of old row i wish to highlight the cell in color red of new row (which is retained after duplicate remove) where values has changed between duplicate old row and new row.
if possible also that changed value insert as comment in new row cell in same column.

Now i am using this macro to identify the duplicate and remove old entries
Sub Delete_Dups_Keep_Last()
Dim i As Long
Dim j As Long
Worksheets("Sheet1").Activate
Dim ROW_DELETED As Boolean
i = 2 'start on second row
Application.ScreenUpdating = False
Do While i <= Sheets("Sheet1").UsedRange.Rows.Count
ROW_DELETED = False
For j = i + 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 3) = Cells(j, 3) Then
Rows(i).Delete
ROW_DELETED = True
Exit For
End If
Next j
If Not ROW_DELETED Then i = i + 1
Loop
Application.ScreenUpdating = True
End Sub

In sheet 1 row 3 and row 9 having duplicate value in column C and wish to highlight and insert changed value as a comment in row 9.




[TABLE="width: 585"]
<colgroup><col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 1824;"> <col width="23" style="width: 17pt; mso-width-source: userset; mso-width-alt: 736;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2016;"> <col width="203" style="width: 152pt; mso-width-source: userset; mso-width-alt: 6496;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1280;" span="2"> <col width="23" style="width: 17pt; mso-width-source: userset; mso-width-alt: 736;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2272;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2016;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2272;"> <tbody>[TR]
[TD="class: xl39180, width: 57, bgcolor: red"]Confirmed delivery date[/TD]
[TD="class: xl39180, width: 23, bgcolor: red"]STP[/TD]
[TD="class: xl39181, width: 63, bgcolor: red"]MO[/TD]
[TD="class: xl39180, width: 203, bgcolor: red"]Long Description[/TD]
[TD="class: xl39180, width: 40, bgcolor: red"]Prod. Line [/TD]
[TD="class: xl39180, width: 48, bgcolor: red"]Prod[/TD]
[TD="class: xl39182, width: 40, bgcolor: yellow"]Area Crop[/TD]
[TD="class: xl39180, width: 40, bgcolor: red"]Order Type[/TD]
[TD="class: xl39182, width: 23, bgcolor: yellow"]Supplier[/TD]
[TD="class: xl39183, width: 71, bgcolor: red"]Finish date (DMY)[/TD]
[TD="class: xl39180, width: 63, bgcolor: red"]Kg's[/TD]
[TD="class: xl39182, width: 40, bgcolor: yellow"]Packages / TH's[/TD]
[TD="class: xl39186, width: 71, bgcolor: yellow"]TH's[/TD]
[/TR]
[TR]
[TD="class: xl39184, bgcolor: transparent"] [/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl39184, bgcolor: transparent"]1337972[/TD]
[TD="class: xl39184, bgcolor: transparent"]WMW XXX [/TD]
[TD="class: xl39184, bgcolor: transparent"]OK[/TD]
[TD="class: xl39184, bgcolor: transparent"]CCP[/TD]
[TD="class: xl39184, bgcolor: transparent"] [/TD]
[TD="class: xl39184, bgcolor: transparent"]XP1[/TD]
[TD="class: xl39184, bgcolor: transparent"] [/TD]
[TD="class: xl39179, bgcolor: transparent, align: right"]2/7/2017[/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]262.761[/TD]
[TD="class: xl39184, bgcolor: transparent"] [/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]2628.661[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39187, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl39187, bgcolor: transparent"]1337975[/TD]
[TD="class: xl39187, bgcolor: transparent"]WMW XXX [/TD]
[TD="class: xl39187, bgcolor: transparent"]OK[/TD]
[TD="class: xl39187, bgcolor: transparent"]CCP[/TD]
[TD="class: xl39185, bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent"]XP1[/TD]
[TD="class: xl39185, bgcolor: transparent"][/TD]
[TD="class: xl39188, bgcolor: transparent, align: right"]2/7/2017[/TD]
[TD="class: xl39187, bgcolor: transparent, align: right"]285.72[/TD]
[TD="class: xl39185, bgcolor: transparent"][/TD]
[TD="class: xl39187, bgcolor: transparent, align: right"]2843.77[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl39184, bgcolor: transparent"]1337976[/TD]
[TD="class: xl39184, bgcolor: transparent"]WMW XXX [/TD]
[TD="class: xl39184, bgcolor: transparent"]OK[/TD]
[TD="class: xl39184, bgcolor: transparent"]CCP[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent"]XP1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39179, bgcolor: transparent, align: right"]2/7/2017[/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]260.381[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]2615.787[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl39184, bgcolor: transparent"]1337978[/TD]
[TD="class: xl39184, bgcolor: transparent"]WMW XXX [/TD]
[TD="class: xl39184, bgcolor: transparent"]OK[/TD]
[TD="class: xl39184, bgcolor: transparent"]CCP[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent"]XP1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39179, bgcolor: transparent, align: right"]2/9/2017[/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]277.352[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]2855.618[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl39184, bgcolor: transparent"]1337980[/TD]
[TD="class: xl39184, bgcolor: transparent"]WMW XXX [/TD]
[TD="class: xl39184, bgcolor: transparent"]OK[/TD]
[TD="class: xl39184, bgcolor: transparent"]CCP[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent"]XP1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39179, bgcolor: transparent, align: right"]2/9/2017[/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]240.667[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]2439.644[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl39184, bgcolor: transparent"]1337981[/TD]
[TD="class: xl39184, bgcolor: transparent"]WMW XXX [/TD]
[TD="class: xl39184, bgcolor: transparent"]OK[/TD]
[TD="class: xl39184, bgcolor: transparent"]CCP[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent"]XP1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39179, bgcolor: transparent, align: right"]2/9/2017[/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]280.345[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]3005.02[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl39184, bgcolor: transparent"]1337982[/TD]
[TD="class: xl39184, bgcolor: transparent"]WMW XXX [/TD]
[TD="class: xl39184, bgcolor: transparent"]OK[/TD]
[TD="class: xl39184, bgcolor: transparent"]CCP[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent"]XP1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39179, bgcolor: transparent, align: right"]2/10/2017[/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]276.225[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent, align: right"]2894.56[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl39187, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl39187, bgcolor: transparent"]1337975[/TD]
[TD="class: xl39187, bgcolor: transparent"]WMW XXX [/TD]
[TD="class: xl39187, bgcolor: transparent"]OK[/TD]
[TD="class: xl39187, bgcolor: transparent"]CCP[/TD]
[TD="class: xl39185, bgcolor: transparent"][/TD]
[TD="class: xl39184, bgcolor: transparent"]XP1[/TD]
[TD="class: xl39185, bgcolor: transparent"][/TD]
[TD="class: xl39189, bgcolor: transparent, align: right"]2/10/2017[/TD]
[TD="class: xl39190, bgcolor: transparent, align: right"]247.926[/TD]
[TD="class: xl39185, bgcolor: transparent"][/TD]
[TD="class: xl39190, bgcolor: transparent, align: right"]2861.062[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in Advance
 
Sort by date Sort by votes
Hi
I am not a regular visitor to this forum & was not aware with this " cross post", can any one have solution for my query?

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
546
rahildhody
R
T
  • Question Question
VBA - INDEXMATCH for a Worksheet Change Event or another approach by not showing already selected items in dropdown
Replies
2
Views
724
mumps
mumps
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
D
  • Question Question
Excel highlight duplicates with unique values.
Replies
1
Views
856
DanteAmor
DanteAmor
P
  • Solved
VBA copy paste cell range below first blank row from one wb to another
Replies
9
Views
303
Joe4
Joe4

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top