Banding like parts

[Jeffrey Green]

I have 4000 rows with hundreds of unique Part numbers. My list is sorted by Part Number.

I might have 1 Part number, I might have 5 the same, I might have 200 the same.

I would like to conditionally format the rows so Like parts are banded together. Blue/White (no formatting) would be fine . . .I just need a visual clue that the part number changed.

Too many to do by hand


Thoughts??

 

[jim may]

With sample data in Sheet1, like so.. Highlight the Range(A4:A16) and Run Code below..

Excel Workbook
	A
1
2
3	PartNumber
4	ABC
5	ABC
6	ABC
7	DDD
8	EEE
9	FFF
10	FFF
11	FFF
12	FFF
13	GG
14	HH
15	HH
16	HH
Sheet1

Excel 2007

Sub ShadeLikeItems()
Dim Rng As Range
Dim OldVal As Variant
Dim Yellow As Boolean

Yellow = True
OldVal = Null
For Each Rng In Selection ' CHANGE TO CORRECT RANGE
    If Rng.Value = OldVal Then
        If Yellow Then
            Rng.Resize(1, 6).Interior.ColorIndex = 6   'Change to suit
        Else
            Rng.Resize(1, 6).Interior.ColorIndex = -4142
        End If
    Else
        OldVal = Rng.Value
        Yellow = Not Yellow
        If Yellow Then
            Rng.Resize(1, 6).Interior.ColorIndex = 6    'change to suit
        Else
            Rng.Resize(1, 6).Interior.ColorIndex = -4142
        End If
    End If
Next Rng
End Sub