Coloring Like Rows Non-Repeating

Background:
I have a workbook that has a huge list of supplies. Each supply has a name and certain supplies are part of a family while others are standalone items. Everything on the list is sorted primarily by family name. Each item in the same family must be highlighted with the same color for each row. The colors can't be repeat back to back [example: Family A is red so Family B can't be red]

The issue:
I created a crude formula that identifies supply families 1-7 and each number corresponds to a different color in conditional formatting. The main issue is that rows get swapped around or are inserted in the middle of the table. This causes the relative reference in the formula to break. I originally attempted to solve this with vba by having the formula re-applied to the column down whenever a row is inserted. The issue is that the real file is several thousand rows which either causes the file to slow down immensely or border-line crash.

I was looking if there was another solution that I didn't consider whether it be formula based or not.

Dear Glenn,

Ah. That is a clever way of doing it. Usually when I use sort and unique, it is to provide a list. That is very clever way of going about it, and your help is greatly appreciated.


Ethan