Non-Conditional Format: Duplicates Values in 1 Column Format Fill and Font Entire Row

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

StjepanJones

Thread Starter
Joined
Jul 25, 2011
Messages
16
Hello,

I'm working on an Excel project. I need a Macro to find duplicate values in Column D and when a duplicate is found, format the font color and fill color for that row, column A thru E. Any ideas?

What I currently have finds the duplicates, but only contionally formats items in that column. I don't want it to be conditional, but here is what I'm using:

Private Sub btnDuplicates_Click()
Columns("D:.D").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("E2").Select
End Sub

Any idea how to do this?
 
Joined
Nov 19, 2010
Messages
185
I got rid of the conditional formatting you recorded, because I thought the best way was to loop through the cells in the column and try to find them in the range above and below, and it found them, to format the range of cells from A:E associated with the cells. The problem with using conditional formatting is that you can't loop through cells containing a certain format, because the cell object is not actually formatted. I hope this helps...

Code:
Sub Test()
Dim cell As Range, strValue As String
For Each cell In Range("D1:D10")
strValue = cell.Value
Select Case cell.Address
Case Is = "$D$1"
If Not Range(cell.Offset(1, 0), cell.End(xlDown)).Find(strValue) Is Nothing Then GoTo Format
Case Else
If Not Range(cell.Offset(-1, 0), cell.End(xlUp)).Find(strValue) Is Nothing Or _
Not Range(cell.Offset(1, 0), cell.End(xlDown)).Find(strValue) Is Nothing Then GoTo Format
Continue:
End Select
Next
 
Exit Sub
Format:
' you can obviously change this to suite your needs
With Range(cell.Offset(0, -3), cell.Offset(0, 1)).Font
.Bold = True
.Size = 10
.ColorIndex = 16
End With
GoTo Continue
 
End Sub
[\CODE]
 

StjepanJones

Thread Starter
Joined
Jul 25, 2011
Messages
16
It looks good. Now, the other question I would have is how I can make it ignore blank cells? When I run this, it's considering them to be duplicates.
 
Joined
Nov 19, 2010
Messages
185
put a conditional if statement in the code before strValue = cell.value... so

If Not cell.value = "" Then
strValue = cell.value
etc.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top