excel group column highlight matching values

I have a alphabetized dataset of:

I'd like to have each group by the first 6 matching characters. So all the values that start with D47089 are blue highlighted. all the D47091 are green highlighted ect. Having it rotate between 3 different colors all the way down the column. (colors aren't important, just that they rotate for easy reading).

Is there a way to have excel do this automatically without me having to create separate conditional rules for each possible iteration of the first 6 characters?

Basic logic is the same as Scot's, but using a named formula:

• To accommodate future entries easily, I would recommend inserting table for the data

• In Table tab clear "Banded Rows" (and other options if needed)

• With a named formula you need to modify it only in one place - so create a named formula, for example, colIndex with

  =LAMBDA(a, MOD(XMATCH(LEFT(a,6), UNIQUE(LEFT(Table1[Column1],6))), 3))
  

• Select all data rows in the table with top left cell being the active cell, add conditional formatting as shown below (with your colors :)

• Formula for formatting is =colIndex($A2)=0