VBA for highlighting needs revising

[estatefinds]

I need revising of this Code, please, to accomedate the data I have.
so I hava data in column E2:E324760 which wont change this is the constant.
now the data I have in E:324761 to unlimited will change.
that data looks like this 1-2-3-4-5

So I need this code Below to be able to search in both ranges, meaning that what ever occurs in the
second range E324761 to what ever,(unlimited data) will have a duplicate in the first range E2:E324760; then the data will be highlighted in the first range cause a duplicate was found in the second range. so any duplicates that occur in column E will be highlighted in the first range E2:E324760.
Thank you!


Sub Highlight_Duplicates_In_Column_E()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
For iCntr = 1 To lastRow
If Cells(iCntr, 5) <> "" Then
matchFoundIndex = _
WorksheetFunction.Match(Cells(iCntr, 5), Range("E1:E" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 5).Interior.Color = vbYellow
End If
End If
Next
Columns("E").EntireColumn.AutoFit
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

[jolivanes]

@ estatefinds
Have you noticed in all your previous posts that people used code tags?
Select the code and click on the # at the top of your box. It's that simple

[snb]

If you need help ask this forum.
If you need code hire a programmer.
If English is not your native language: there are many forums in many languages, probably also yours.

[estatefinds]

Sub Highlight_Duplicates_In_Column_E()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
For iCntr = 1 To lastRow
If Cells(iCntr, 5) <> "" Then
matchFoundIndex = _
WorksheetFunction.Match(Cells(iCntr, 5), Range("E1:E" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 5).Interior.Color = vbYellow
End If
End If
Next
Columns("E").EntireColumn.AutoFit
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Formatting tags added by mark007

[estatefinds]

I need help with this code, Also I can see why you would think English wasn't my native language due to the typos.

[estatefinds]

I need help with this code, Also I can see why you would think English wasn't my native language due to the typos.
English Is my native language. I was typing fast trying to get the description out. Thank you

[PAB]

Hi Dennis,

Firstly, when you post code you need to use the code tags, as described by jolivanes in post #2.

The other option is to type CODE at the beginning of the code and /CODE at the end of the code with the [] brackets around both.

Secondly, here is some code that will highlight rng1 where duplicates are found in rng2, both rng's are in column "E".


Sub Highlight_Duplicates()
' ---------------------------------------------------------------------------------------
' Highlight rng1 where duplicates are found in rng2, both rng's are in column "E".
' ---------------------------------------------------------------------------------------
Dim rng1 As Range, rng2 As Range
Dim c As Range
Dim cfind As Range
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Set rng1 = Range("E2:E324760")
Set rng2 = Range("E324761:E750000")
Columns("E:E").Interior.ColorIndex = xlNone
For Each c In rng1
If c <> "" Then
Set cfind = rng2.Cells.Find(what:=c.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then c.Interior.Color = vbYellow
End If
Next c
Columns("E").EntireColumn.AutoFit
Set rng1 = Nothing
Set rng2 = Nothing
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

I hope this helps!

[estatefinds]

Hi Thanks I wasn't familiar with the code tags I did go to code and click the #6 and it just opened the screen with code on it.
bu the description you gave me seems easier by using the CODE and Brackets around both. im still learning the other parts of this website. Thanks for the information. Also Ill test code tonight if there are any issues if that's ok. Thanks Again!!!

[estatefinds]

Hi Thanks I wasn't familiar with the code tags I did go to code and click the #6 and it just opened the screen with code on it.
but the description you gave me seems easier by using the CODE and Brackets around both. im still learning the other parts of this website. Thanks for the information. Also Ill test code tonight, and let you know if there are any issues, if that's ok. Thanks Again!!!

[PAB]

OK, good luck!

[snb]

Conditional formatting

select Range("E2:E324760")

add rule
=COUNTIF("$E$324761:$E$750000";$E2)>0

applies to
$E$2:$E$324760

format
eg. fill orange.

[PAB]

VERY nice snb :yes, I always forget about conditional formatting :banghead:.

[estatefinds]

Conditional formatting takes too long on the data I have, but thanks

[snb]

Not with the formula I gave you.

[estatefinds]

Ok I'll try the vba which I prefer and the formula thank you!

[jolivanes]

@snb
I like your choice of colo(u)r. Orange.
Any particular reason for that like the "House of Orange"?

[snb]

I like the fragrance of orange-blossom.. And I prefer colour to color.

[estatefinds]

program works great!
Thank you!!!

[PAB]

Program works great! Thank you!!!
You're welcome.
Thanks for the feedback.

[snb]

@PAB


Sub M_snb()
sn = Range("E2:E324760")
sp = Range("E324761:E750000")

for j=1 to ubound(sn)
if not iserror(application.match(sn(j,1),sp,0)) then cells(j+1,5).interior.colorindex=5
next
End Sub

[PAB]

I will get the hang of VBA one day :banghead:.
Very nicely done snb, thanks for posting that, it is appreciated and will go into my code bank for the future if needed :yes.