Solved: VBA Highlight row

[Emoncada]

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.

[Emoncada]

BUMP

[austenr]

what version of excel do you have?

[Emoncada]

2007

[Simon Lloyd]

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!

[Emoncada]

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.

[Simon Lloyd]

Then use conditional formatting on the column, is there a specific reason why you want to highlight the entire row?

[Emoncada]

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.

[Simon Lloyd]

Ok, not tested but try this:
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

[Emoncada]

That works. thanks Simon

[Simon Lloyd]

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.