VBA Interior ColorIndex not working on a match

[storm925]

Hi All,

Below is my code that I can't get to work...

Sub find_dup_pairs()


Dim i As Long
Dim LastRow As Long


LastRow = Range("B" & Rows.Count).End(xlUp).Row


For i = LastRow To 1 Step -1
    If Cells(i, 2) & Cells(i, 3) = Cells(i + 1, 2) & Cells(i + 1, 3) Then
        'Range(Cells(i, 2), Cells(i, 3)).Select   'this works...
        Range(Cells(i, 2), Cells(i, 3)).Interior.ColorIndex = 255   'this does not work...
    End If
Next i
End Sub

The code works if I want to "Select" cells B and C if there is a duplicate. When I turn off the duplicate, and just want to highlight the duplicate pairs, an error message is thrown that says "subscript out of range". I would like to highlight pairs without having to select each one if that is possible. Thanks for reading my post and your help on this. --Ben

 

[Fluff]

ColorIndex is a whole number from 1 to 56 inclusive. Therefore you cannot use 255

Have a look here for more info on colours
http://dmcritchie.mvps.org/excel/colors.htm

 

[storm925]

Thank you for the response. I changed the number to 6 (for yellow), but I still get the same error.

 

[Fluff]

Your code works for me.
Do you have any merged cells?

 

[storm925]

I'm really embarrassed to say this, but my sheet had restrictions on it. Once I removed the restrictions, it worked. Thanks again for looking at my code and I'm sorry to waste your time... feeling quite dumb right now.

 

[Fluff]

No worries
Glad to help & thanks for the feedback

 

[My Aswer Is This]

For a quick reference to see all the Colorindex numbers run this small script:

Sub Color_Index_Me()
Dim i As Long
Dim c As Long
c = ActiveCell.Column
    For i = 1 To 56
        With Cells(i, c)
            .Value = i
            .Interior.ColorIndex = i
            .Font.Size = 18
        End With
    Next
End Sub