Highlight rows with same colors if cells (Column G) share the same content

[cara66]

Hello everyone,

I would appreciate if anyone can help me with this problem -

I'd like to highlight rows with same content (in cells) in column G with the same color (could be random) -

1. if cells have the same content, the rows should have the same color
2. colors should be differnt
3. for rows that only have blank cells, rows should be left uncolored


eg
G
row1 apple
row2 pear
row3
row4 pear
row5 apple


row1 and 5 should be highlighted with a color and row 2 and 4 should be highlighted with another color;
row3 will be left blank.

========================================================

I have written something and would appreciate if you could help me debug it


Thank you very much!!!

Sub colours()

Dim I As Integer
Dim j As Integer

For I = 3 To 65536
If Cells(I, "N").Value <> "" Then For j = 1 To 65536 And j < I
If Cells(I, "N").Value = Cells(j, "N").Value Then Cells(I, "N").EntireRow.Interior.ColorIndex = j

Exit Function

Else: Next j

End If

For I = 3 To 65536
If Cells(I, "N").Value <> "" Then For j = 1 To 65536 And j < I
If Cells(I, "N").Value <> Cells(j, "N").Value Then Cells(I, "N").EntireRow.Interior.ColorIndex = I


Next I

End Sub

[Cheeky Charlie]

Please use code tags.

Your code checks every single cell in the column - and checks it against every single row aswell - this seems a somewhat clunky way of doing it...

Why not sort the data and just colour it in batches? You could then loop through in sections and only change the colour when you hit a new entry in column G?

If you wanted to retain the order you could add a "sort column" numbered 1 - end of rows and re-sort your data by this column after colouring.

Better approach?