×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Back coloring an entire row if a duplicate is found in a dynamic range

thread707-1826186

Back coloring an entire row if a duplicate is found in a dynamic range

Back coloring an entire row if a duplicate is found in a dynamic range

(OP)
I need to determine if the current row (iRow), column A, is duplicated anywhere in the range of A11:A & Range("E7").Value.

E7 will contain a value that will indicate the end of the range. This could realistically be from 11 to maybe 10,000. It will change every time.

The SetGLRRowColor() sub creates the desired back color and is working fine.

So to reiterate, I need to replace my 1=1 with some logic to determine if the value in column A in that row (iRow), is a duplicate anywhere in the range. I can't work with just a true/false result that duplicates exist, I need a true/false result and the line number, one row at a time.

Here is my code:

CODE

'check for duplicate customer numbers
 For iRow = 11 To Range("E7").Value
    If iRow = 11 Then
        'do nothing, duplicates not possible on first row
    Else
        If 1 = 1 Then 'need code here
            Call SetGLRRowColor("YES", "Duplicate Account", iRow)
        Else
            Call SetGLRRowColor("NO", "", iRow)
        End If
    End If
Next iRow

Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Back coloring an entire row if a duplicate is found in a dynamic range

Have you had a look at conditional formatting for setting the colour?

RE: Back coloring an entire row if a duplicate is found in a dynamic range

(OP)
I have, but for various complicated reason, I need code to make my example above work.

Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Back coloring an entire row if a duplicate is found in a dynamic range

Yes, and you can automate conditional formatting with VBA, e.g

CODE --> 

' Simple example, just sets colour of duplicates in column a
Sub Example()

    Dim CondFormat As FormatConditions
    
    Set CondFormat = Range("$A$11:$A" & Range("E7").Value).FormatConditions
    
    With CondFormat
        If .Count > 0 Then .Delete ' clean up existoing conditional formatting applied to range
        .Add xlUniqueValues
        .Item(1).DupeUnique = xlDuplicate
        .Item(1).Interior.Color = RGB(255, 0, 0)
    End With

End Sub

RE: Back coloring an entire row if a duplicate is found in a dynamic range

(OP)
Strongm,

Will that highlight the entire row? Or just the cell?

I need the entire row highlighted. I have the code to do that with my SetGLRRowColor() subroutine. But I do not know how to return a true/false on duplicates and the line number.

Any ideas?

Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Back coloring an entire row if a duplicate is found in a dynamic range

Application.Worksheetfunction.Countif(you can figure out what goes here) > 1

RE: Back coloring an entire row if a duplicate is found in a dynamic range

If for iRow=11 'do nothing': maybe start from 12?

Is the data in column A sorted? If yes, then you can build a single loop and test if value in row i equals to values in row i-1 or i+1, if yes - this row has duplicates, otherwise no.

If the data is not sorted, I would pick the data in range to variant array (v=Range("A11:A" & Range("E7")) create helper array with duplicate marks (double loop required to test: For i=1 to N and, inside, For j=i+1 to N. Skip marked as duplicated items found in loop with i), and finally loop through the helper array and to format corresponding rows.

combo

RE: Back coloring an entire row if a duplicate is found in a dynamic range

Or, move back a step. or two...
The best way to prevent duplicates is to not allowed them to happen in the first place.

Just my opinion.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Back coloring an entire row if a duplicate is found in a dynamic range

>Will that highlight the entire row?

No, just the cell - but, as I say, it was just a simple example. here's a fractionally more complicated version that does entire row ...

CODE --> 

Sub Example2()

    Dim CondFormat As FormatConditions
    
    Set CondFormat = Range("$A$11:$C" & Range("E7").Value).EntireRow.FormatConditions
    
    With CondFormat
        If .Count > 0 Then .Delete ' clean up existing conditional formatting applied to range
        .Add xlExpression, , "=COUNTIF($A$11:$A$" & Range("E7") & ",$A11)>1"
        .Item(1).Interior.Color = RGB(255, 255, 0)
    End With

End Sub

RE: Back coloring an entire row if a duplicate is found in a dynamic range

(OP)
Solved.

FWIW here was my solution:

CODE

'check for duplicates
iRow = 11
Do While Cells(iRow, 1).Value <> ""
    If iRow = 11 Then
        'do nothing, duplicates not possible on first row
    Else
        If Application.WorksheetFunction.CountIf(Range(Cells(11, 1), Cells(Range("E7").Value, 1)), Cells(iRow, 10)) > 1 Then
            Call SetGLRRowColor("NO", "Duplicate Old Account", 11)
            Call SetGLRRowColor("NO", "Duplicate Old Account", iRow)
        Else
            Call SetGLRRowColor("YES", "", iRow)
        End If
    End If
    iRow = iRow + 1
Loop

Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Back coloring an entire row if a duplicate is found in a dynamic range

Like combo said, why not ponder

CODE

'check for duplicates
iRow = 12
Do While Cells(iRow, 1).Value <> ""
    If Application.WorksheetFunction.CountIf(Range(Cells(11, 1), Cells(Range("E7").Value, 1)), Cells(iRow, 10)) > 1 Then
        Call SetGLRRowColor("NO", "Duplicate Old Account", 11)
        Call SetGLRRowColor("NO", "Duplicate Old Account", iRow)
    Else
        Call SetGLRRowColor("YES", "", iRow)
    End If
    iRow = iRow + 1
Loop

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Back coloring an entire row if a duplicate is found in a dynamic range

>my solution:

Couple of questions/thoughts

1) You are calling

Call SetGLRRowColor("NO", "Duplicate Old Account", 11)

for every duplicate found. By which I mean if you had

1
1

then it would be called twice. And in the extreme case that 10000 (your top level estimate) all happened to be duplicates, this would be called 10000 times. Is that really what you want to do?

2) And

'do nothing, duplicates not possible on first row

Well, see previous question.

Call SetGLRRowColor("NO", "Duplicate Old Account", 11)

is doing the same thing as

Call SetGLRRowColor("NO", "Duplicate Old Account", iRow)

if iRow was 11.

3) Have you checked performance with 10000 rows? You are iterating each line ...


Just some food for thought

RE: Back coloring an entire row if a duplicate is found in a dynamic range

(OP)
I actually noticed that and cleaned it up. It was something residual that I had left there by accident.

And yes, I want to check all 10,000 lines. A simple "there are duplicates" message does not help.

Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Back coloring an entire row if a duplicate is found in a dynamic range

>And yes, I want to check all 10,000 lines

yes, I understood that (and the potential conditional formatting solution does that without a VBA loop). It was setting the exact same line 10000 times that seemed off

RE: Back coloring an entire row if a duplicate is found in a dynamic range

VBA is fast enough. This code below processed 10k cells with worst case - no duplicates, in 5 seconds:

CODE --> 

Sub test()
Dim i As Integer, j As Integer
Dim iMin As Integer, iMax As Integer
Dim vRange As Variant, bMarker() As Boolean
Debug.Print Now()
vRange = ThisWorkbook.Worksheets(1).Range("A1:A10000")
iMin = 1
iMax = UBound(vRange, 1)
ReDim bMarker(iMin To iMax)
For i = iMin To iMax
    If bMarker(i) = False Then
        For j = i + 1 To iMax
            If vRange(i, 1) = vRange(j, 1) Then
                bMarker(i) = True
                bMarker(j) = True
            End If
        Next j
    End If
Next i
With ThisWorkbook.Worksheets(1)
    For i = iMin To iMax
        If bMarker(i) Then .Rows(i).Interior.ColorIndex = 3
    Next i
End With
Debug.Print Now()
End Sub

combo

RE: Back coloring an entire row if a duplicate is found in a dynamic range

Not completely convinced that your example is a good analogue for what the OP's code. And 5 seconds seems a long time to me!

RE: Back coloring an entire row if a duplicate is found in a dynamic range

(OP)
5 seconds is fine compared to how long it would take to key this in to the ERP manually.

Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Back coloring an entire row if a duplicate is found in a dynamic range

The conditional format is pretty much instantaneous ... (but may not do all that you think you want)

RE: Back coloring an entire row if a duplicate is found in a dynamic range

So what data does SetGLRRowColor() produce beyond the conditional formatting? Your SetGLRRowColor() function is a Black Box.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close