Posts by Mike Hunt

Hi KjBox,

Oops I forgot to edit it to the last revision I used:

Option Explicit
Sub Color_Row()
Dim LastRow As Long
Dim ColorFlag As Boolean
Dim I As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ColorFlag = False
For I = 2 To LastRow
If (Cells(I, "A") <> Cells(I - 1, "A")) Then ColorFlag = Not (ColorFlag)
If (ColorFlag) Then
With Rows(I).Interior
.ColorIndex = Application.RandBetween(1, 56)
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next I
End Sub

Kind regards,

Mavlon

Hi KjBox,

I made the change to .ColorIndex = Application.RandBetween(1, 56) but it changed the colors when the part number was the same and then skipped a number leaving it with a white background for next number and then continues with multicolor for next part number etc..

Column A was the result. I edited Column B to show what I am trying to achieve:

Thank you for your help.

Kind regards,

Mavlon

Hello Excel Gurus,

Can you please help update this so that it uses a random different color in the color index each time the value changes? The way it is working now is that it alternates between gray to white. I am trying to make it change to a different color every time the value changes all the way down a column.

This works except for the color index:

Option Explicit
Sub Color_Row()
Dim LastRow As Long
Dim ColorFlag As Boolean
Dim I As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ColorFlag = False
For I = 2 To LastRow
If (Cells(I, "A") <> Cells(I - 1, "A")) Then ColorFlag = Not (ColorFlag)
If (ColorFlag) Then
With Rows(I).Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next I
End Sub

Thank you very much.

Mavlon

Hi PCI,

Can you please update this so that it uses a different color in the color index? The way it is working now is that it alternates between gray to white. I am trying to make it change to a different color everytime the value changes all the way down a column.

This works except for the color index:

1. Option Explicit
2. Sub Color_Row()
3. Dim LastRow As Long
4. Dim ColorFlag As Boolean
5. Dim I As Long
6. LastRow = Range("A" & Rows.Count).End(xlUp).Row
7. ColorFlag = False
8. For I = 2 To LastRow
9. If (Cells(I, "A") <> Cells(I - 1, "A")) Then ColorFlag = Not (ColorFlag)
10. If (ColorFlag) Then
11. With Rows(I).Interior
12. .ColorIndex = 15
13. .Pattern = xlSolid
14. .PatternColorIndex = xlAutomatic
15. End With
16. End If
17. Next I
18. End Sub