1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by StjepanJones, Aug 25, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. StjepanJones

    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?
     
  2. scotty718

    scotty718

    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]
     
  3. StjepanJones

    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.
     
  4. scotty718

    scotty718

    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.
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1014308

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice