conditional Formatting based on value change

[Houscoogs]

I have a spreadsheet that has over 500 names in column A and I want the color to change once the names change.

Example:

Cell: A1 = John
A2 = John
A3 = Ben
A3 = Ben
A3 = Ben

I want the color of John to be the same and once the next cell down changed, I want it to color differently than the previous set.

Please help.

 

[Joe Was]

This highlights the list breaks:


Sub myfindGroups()
Dim myEnd As Boolean
Dim x&

Columns("A:A").Interior.ColorIndex = xlNone

x = 1

While Not myEnd
cRng = Cells(x, 1)
cRng2 = Cells(x + 1, 1)

If cRng2 = "" Then myEnd = True

If cRng = cRng2 And myFlag = False Then
n = Cells(x, 1).Row
myFlag = True
End If

If (((cRng <> cRng2) And n <> 1) Or (x = 1 And cRange <> cRng2)) Then

If x = 1 Then
If Cells(x, 1).Interior.ColorIndex <> 36 Then Cells(x, 1).Interior.ColorIndex = 36
n = 0
Else

If Cells(x, 1).Interior.ColorIndex <> 36 Then Cells(x, 1).Interior.ColorIndex = 36
n = n - 1
End If

MsgBox "Number of Records for: " & _
Cells(x, 1) & ", is " & Cells(x, 1).Row - n
myFlag = False
End If

x = x + 1
Wend
End Sub