Consulting

Results 1 to 11 of 11

Thread: Solved: VBA Highlight row

  1. #1
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location

    Solved: VBA Highlight row

    I have a range of rows A3:O1980.

    What I would like to do is have a vba script run on change and see if a row has a duplicate.

    If So I would like for it to highlight the "matching row" range A:O

    *Not sure if this part is possible
    Now if possible I would like to highlight them a different color pending if it's the 2nd duplicate, 3rd duplicate and 4th duplicate.

  2. #2
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    BUMP

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    what version of excel do you have?
    Peace of mind is found in some of the strangest places.

  4. #4
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    2007

  5. #5
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Why not use conditional formatting, xl2007 & xl2010, highlight your first row, goto Home tab>Conditional Formatting>Highlight Cell Rules>Duplicate Values. Then copy formats down!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  6. #6
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    No that will highlight what's duplicate in that row.
    What I need it to highlight the duplicates in one main column.

    Column B if duplicates are found in Column B then Highlight row of duplicate.

  7. #7
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Then use conditional formatting on the column, is there a specific reason why you want to highlight the entire row?
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  8. #8
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    I would like to highlight the range of A:O of the duplicate row mainly because it's more visible no matter where I am on the spreadsheet if I scroll Right I won't be able to see Column B unless I do a Freeze Panel or something.

  9. #9
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Ok, not tested but try this:
    [VBA]Sub Highlight_Duplicates()
    Dim Rng As Range, MyCell As Range
    Set Rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    For Each MyCell In Rng
    If Application.WorksheetFunction.CountIf(Rng, MyCell) > 1 Then
    Range(MyCell.Address & ":" & "O" & MyCell.Row).Interior.ColorIndex = 6
    Else
    MyCell.EntireRow.Interior.ColorIndex = xlNone
    End If
    Next MyCell
    End Sub[/VBA]
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  10. #10
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    That works. thanks Simon

  11. #11
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Marking your thread solved.
    If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution -- chances are some other member will benefit. If your question has been solved by someone then please use Thread Tools at the top of the thread and Mark Solved then click Perform Action.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •