Highlighting Duplicate Values In Each Row of a Multiple Row Worksheet

0

I am attempting to create an excel macro to highlight the duplicate values in a rows range of values.  I was able to figure out how to get the macro to work for one row at a time, by manually changing the row range for each row (see 'Exhibit 1 - vba Code' -  tagged code below). But I have not been able figure out how to get the macro to highlight duplicate values across each and every individual rows range of a 1000+ row worksheet.  The goal is to get the macro to run through the entire worksheet and highlight duplicate values contained within each individual row.  I attached a 'Small Scale Example of the desired result'.  Does anyone know how to accomplish this?  Thank you in advance!

Exhibit 1 – vba Code:

[CODE]

Sub Macro1()

' Macro1 Macro

' UpdatebyExtendoffice20161222

Dim x As Integer

Application.ScreenUpdating = False

' Set numrows = number of rows of data.

NumRows = Range("b20:by20", Range("b20:by20").End(xlDown)).Rows.Count

' Select cell b20.

Range("b20:by20").Select

' Establish "For" loop to loop "numrows" number of times.

For x = 1 To NumRows

 Selection.FormatConditions.AddUniqueValues   
             Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

 Selection.FormatConditions(1).DupeUnique = xlDuplicate

 With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

End With

With Selection.FormatConditions(1).StopIfTrue = False

' Selects cell down 1 row from active cell.

ActiveCell.Offset(1, 0).Select

Next

Application.ScreenUpdating = True

End Sub  

Small Scale Example of the desired result: Attached

Answer
Discuss

Answers

0
Selected Answer

A Selection is a Range made visible on the sheet. Application.ScreenUpdating = False makes the Selection invisible. What's the point? Stop thinking in terms of selecting anything. Instead, focus on defining Range objects and manipulating them.

The task is to create a Range object for each row.

Dim Rng As Range
Dim NumRows As Long
Dim R As Long

NumRows = Range("B2:BY20").Rows.Count
For R = 2 To NumRows
    Set Rng = Range(Cells(R, 2), Cells(R, Columns("BY").Column))
    Debug.Print Rng.Address
Next R

The above code will set a range and print its address in the Immediate Window. Of course, you might also apply conditional formatting to it.

The above code is also an example of how to post code on this forum. To enable the effect of copying the code easily please enclose the entire code in one set of code brackets. Had you done that (and you might still do, if you like) I would have commented your code.

Edit 25 Oct 2018  =================================

The code below combines your original code for setting the CF (slightly reformatted) but applied to a dynamic Range object instead of a static Selection object.

Sub ApplyCF()
    ' 25 Oct 2018
    
    Dim Ws As Worksheet
    Dim Rng As Range                    ' variable range
    Dim Rl As Long                      ' last used row
    Dim R As Long                       ' row counter
    Dim Idx As Integer                  ' FormatConditions index
    
    Set Ws = ActiveSheet                ' better: Set Ws = Worksheets("SheetName")
                                        ' to prevent code being run on the wrong sheet
    Application.ScreenUpdating = False
    Rl = Ws.Cells(Ws.Rows.Count, "B").End(xlUp).Row     ' last used row in column B
    
    For R = 2 To Rl                                     ' start from row 2 (change as required)
        Set Rng = Range(Ws.Cells(R, "B"), Ws.Cells(R, "BY"))
        With Rng.FormatConditions
            .AddUniqueValues
            Idx = .Count
            With .Item(Idx)
                .SetFirstPriority
                .DupeUnique = xlDuplicate
                With .Font
                    .Bold = True
                    .Italic = False
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0
                End With
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535
                    .TintAndShade = 0
                End With
                .StopIfTrue = False
            End With
        End With
    Next R
    
    Application.ScreenUpdating = True
End Sub
Discuss

Discussion

Thank you for the response Variatus - very much appreciated!   As you probably have gathered I am not an experienced vba coder. I fixed the tagging as you suggested.  I would like to see how your range object solution would be added to the code with the existing conditional formating included in the code.  When you stated "The task is to create a Range object for each row" do you mean I need to add a line of code for each row or can the code be written in such a way that it will perform the row by row (range by range) dup find and conditional formatting through the entire worksheet and stop, for instance, when a blank cell is encountered at the bottom of the sheet?  Thanks again.
Mnoakes (rep: 2) Oct 24, '18 at 11:10 pm
No. Please study the code snippet I posted yesterday. In VBA a range is set much like in Excel, e.g. A1:B12, meaning from [First cell] to [Last cell]. Cells(1,1) specifies A1 (row first, column second). Therefore Cells(12, 2) specifies B12. You can also write Cells(12, "B") but you can't calculate "B" and therefore 12 is more efficient.
In Set Rng = Range(Cells(R, 2), Cells(R, Columns("BY").Column)) the expression Columns("BY").Column calculates the number of the column BY. R is changing with each loop. Therefore you don't need to write a separate line of code for each row's range. The loop is doing that job.
Variatus (rep: 2504) Oct 25, '18 at 5:25 am
Hi Variatus,
Thank you again.  This was very helpful.
Mnoakes (rep: 2) Oct 27, '18 at 11:39 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login